In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
-- create a table FAMILYINF
CREATE TABLE FAMILYINFO (FNO NUMBER CONSTRAINT FC001 PRIMARY KEY,-- constrains field fno to primary key FNAME VARCHAR2 (20) CONSTRAINT FC002 NOT NULL,-- sets field FNAME constraint to "cannot be empty" SEX VARCHAR2 (20) DEFAULT 'MALE'CONSTRAINT FC003 CHECK (SEX IN (' MALE','FEMALE'))
,-- set the constraint of field 'SEX'' to 'gender by default' MALE', if you fill in it yourself, you can only fill in 'MALE' or' FEMALE'.
FAGE NUMBER, REL VARCHAR2 (20),-- Family membership field HOBITNO NUMBER CONSTRAINT FC004 REFERENCES HOBITINFO (HNO) ON DELETE SET NULL)
-- the constraint is set to the foreign key of the field in the table 'HOBITINFO'' HNo', and the null value is set when the field in the reference table is deleted
-- create a list of interests HOBITINF
CREATE TABLE HOBITINFO (HNO NUMBER CONSTRAINT HC001 PRIMARY KEY,-- set 'HNO' as the primary key HNAME VARCHAR2 (20) CONSTRAINT HC002 NOT NULL-- and set the' HNAME' constraint'to 'cannot be empty')
-- Delete table HOBITINFO
DROP TABLE HOBITINFO
-- add information
INSERT INTO HOBITINFO VALUES (1meme Movie); INSERT INTO HOBITINFO VALUES (2meme CG')
-- DDL ALTER TABLE (modify table)
ALTER TABLE FAMILYINFO RENAME TO PCFAMILY;-- renamed the table name of FAMILYINFO to PCFAMILYALTER TABLE PCFAMILY ADD (--add columns, that is, fields and their attributes HEIGH VARCHAR2 (5), SX VARCHAR2 (10) CONSTRAINT PC002 NOT NULL-- field SX creates "cannot be empty" constraints); ALTER TABLE PCFAMILY MODIFY (--modify field properties HEIGH CHAR (20)); ALTER TABLE PCFAMILY DROP COLUMN HEIGH;-- delete field HEIGHALTER TABLE PCFAMILY RENAME COLUMN SX TO SX1 -- modify constraint field name ALTER TABLE PCFAMILY DROP COLUMN SX1;-- delete constraint field-- DROP TABLE (delete table) of DDL (delete table) TRUNCATE TABLE of DROP TABLE HOBITINFO CASCADE CONSTRAINTS;--DDL clear table record TRUNCATE TABLE PCFAMILY
-- INSERT of DML (insert, add record)
INSERT INTO PCFAMILY (FNO,SEX,FNAME,FAGE) VALUES (4 DAYE',45+20)
-- if you specify the order of the fields to be inserted, the information in the values will be filled in against the fields specified earlier.
INSERT INTO PCFAMILY VALUES ();-- Student form CREATE TABLE STUDENT (SNO NUMBER CONSTRAINT SC001 PRIMARY KEY, SNAME VARCHAR2 (10) CONSTRAINT SC002 NOT NULL, SSEX VARCHAR (10) CONSTRAINT S003 CHECK (SSEX IN ('Maure.'
-- teacher's watch
CREATE TABLE TEACHER (TNO NUMBER CONSTRAINT TC001 PRIMARY KEY, TNAME VARCHAR2 (10) CONSTRAINT TC002 NOT NULL)
-- course schedule
CREATE TABLE COURSE (CNO NUMBER CONSTRAINT CC001 PRIMARY KEY, CNAME VARCHAR2 (20) CONSTRAINT CC002 NOT NULL, TNO NUMBER CONSTRAINT CC003 REFERENCES TEACHER (TNO))
);
-- score sheet
CREATE TABLE SCORE (SNO NUMBER CONSTRAINT SSC001 REFERENCES STUDENT (SNO), CNO NUMBER CONSTRAINT SSC002 REFERENCES COURSE (CNO), GRADE VARCHAR2 (20), CONSTRAINT SSC003 PRIMARY KEY (SNO,CNO))
-- querying whether the table has been successfully established already exists
SELECT * FROM STUDENT;SELECT * FROM TEACHER;SELECT * FROM COURSE;SELECT * FROM SCORE;DROP TABLE STUDENT;DROP TABLE TEACHER;DROP TABLE COURSE;DROP TABLE SCORE;INSERT INTO STUDENT VALUES (1memaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa INSERT INTO COURSE VALUES (1); INSERT INTO COURSE VALUES (2); INSERT INTO COURSE VALUES (3); INSERT INTO SCORE VALUES (1); INSERT INTO SCORE VALUES (2); INSERT INTO SCORE VALUES (3);-- UPDATEUPDATE SCORE SET GRADE=GRADE-1;UPDATE SCORE SET GRADE=GRADE+1 WHERE SNO=2;UPDATE SCORE SET GRADE=GRADE+1 WHERE SNO=2 AND CNO=3 of DML
-
DROP deletion is physical deletion, delete deletion is logical deletion.
-- SELECT of DQL
SELECT CHR (107th) HAHA FROM DUAL;SELECT CURRENT_DATE FROM DUAL;SELECT TRUNC (ABS (MONTHS_BETWEEN (DATE'1998-08-08immediate date) / 12)) AGE FROM DUAL;SELECT SNO AS student number, SNAME student name FROM STUDENT;SELECT ALL SSEX FROM STUDENT;SELECT DISTINCT SSEX FROM STUDENT;SELECT UNIQUE SSEX FROM STUDENT;SELECT * FROM SCORE WHERE GRADE
< 60;SELECT ROWID,ROWNUM,STUDENT.* FROM STUDENT;SELECT * FROM STUDENT WHERE ROWNUM = 1;SELECT * FROM STUDENT WHERE ROWNUM 80;SELECT SSEX,COUNT(SNO) FROM STUDENT GROUP BY SSEX;SELECT SNO FROM SCORE WHERE CNO = 'C001' AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = 'C001');SELECT SNO FROM SCORE WHERE CNO = 'C001' AND GRADE >= ALL (SELECT GRADE FROM SCOREWHERE CNO = 'C001'); SELECT * FROM SCORE ORDER BY CNO ASC,GRADE DESC;SELECT * FROM STUDENT,SCOREWHERE STUDENT.SNO = SCORE.SNO;SELECT SNAME FROM STUDENT,SCOREWHERE STUDENT.SNO = SCORE.SNO AND CNO =' C001' AND GRADE = (SELECT MAX (GRADE) FROM SCOREWHERE CNO = 'C001'); SELECT * FROM STUDENT INNER JOIN SCORE ON STUDENT.SNO = SCORE.SNO;SELECT * FROM STUDENT INNER JOIN SCORE USING (SNO); SELECT * FROM STUDENT CROSS JOIN SCOREWHERE STUDENT.SNO = SCORE.SNO SELECT * FROM STUDENT NATURAL INNER JOIN SCORE;SELECT * FROM STUDENT LEFT OUTER JOIN SCORE ON STUDENT.SNO = SCORE.SNO;SELECT * FROM STUDENT RIGHT OUTER JOIN SCORE USING (SNO); SELECT * FROM STUDENT FULL OUTER JOIN SCORE USING (SNO);-- query the name of the student who failed the examination and the name of the failing subject SELECT SNAME,CNAMEFROM (SCORE LEFT JOIN STUDENT USING (SNO)) LEFT JOIN COURSE USING (CNO) WHERE GRADE
< 60;-- 查询所有java考试成绩高于平均分的学生的姓名和成绩SELECT SNAME,GRADEFROM (SCORE LEFT JOIN STUDENT USING(SNO)) LEFT JOIN COURSE USING(CNO)WHERE CNAME = 'JAVA'AND GRADE >(SELECT AVG (GRADE) FROM SCORE LEFT JOIN COURSE USING (CNO) WHERE CNAME = 'JAVA'); select * from (SELECT * from test RIGHT JOIN t_grade using (ID)) a where `Math' > 90Lok-query the student number SELECT SNO FROM (SELECT SNO,CNO FROM SCORE WHERE CNO = 'C001') INNER JOIN (SELECT SNO,CNO FROM SCORE WHERE CNO =' C002') USING (SNO) who has taken both the C001 and C002 subject examinations. -- query the name of the teacher SELECT TNAME,CNAME FROMTEACHER LEFT JOIN COURSE USING (TNO) WHERE CNO IN (SELECT CNO FROM SCORE LEFT JOIN STUDENT USING (SNO) WHERE SNAME = 'LILI' AND GRADE = (SELECT MAX (GRADE) FROM SCORE LEFT JOIN STUDENT USING (SNO) WHERE SNAME =' LILI') of all the subject exams that lili students have taken. -query how many CREATE TABLE CLOTHER_STORE (CTYPE VARCHAR2 (10), STORENUM NUMBER); CREATE TABLE CLOTHER_SALE (CTYPE VARCHAR2 (10), SALENUM NUMBER); INSERT INTO CLOTHER_STORE VALUES ('COAT',700); INSERT INTO CLOTHER_SALE VALUES (' Tashi Shiqiang); INSERT INTO CLOTHER_SALE VALUES ('Tashi Shirtsu); INSERT INTO CLOTHER_SALE VALUES (' COAT',90) INSERT INTO CLOTHER_SALE VALUES ('COAT',78); SELECT CTYPE,STORENUM-SNUM AS KCSY FROMCLOTHER_STORE LEFT JOIN (SELECT CTYPE,SUM (SALENUM) SNUM FROMCLOTHER_ SALE GROUP BY CTYPE) USING (CTYPE);-- switch to scott user SELECT * FROM EMP;SELECT * FROM DEPT
-- gorgeous dividing line--
Here is the content of the SQL exercise
List all departments that have at least one employee.
SELECT DISTINCT DEPTNO,DNAME FROMEMP LEFT JOIN DEPT USING (DEPTNO)
-- 2. List all employees who earn more than "SMITH".
SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH')
3. List the names of all employees and their immediate superiors.
SELECT E.ENAME employee name, B.ENAME superior name FROMEMP E LEFT JOIN EMP B ON E.MGR = B.EMPNO
-- 4. List all employees whose employment date is earlier than their immediate superior.
Name of SELECT E.ENAME employee, date of employment of E.HIREDATE employee, name of B.ENAME superior, date of employment of B.HIREDATE superior FROM EMP E LEFT JOIN EMP B ON E.MGR = B.EMPNOWHERE E.HIREDATE
< B.HIREDATE; --5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。 SELECT DNAME,EMP.* FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; --6. 列出所有"CLERK"(办事员)的姓名及其部门名称。 SELECT ENAME,DNAME FROM EMP LEFT JOIN DEPT USING(DEPTNO) WHERE JOB = 'CLERK'; --7. 列出最低薪金大于1500的各种工作。 SELECT JOB FROM EMP GROUP BY JOB HAVING MIN(SAL) >1500
-- 8. List the names of employees working in the department "SALES" (sales department), assuming that you do not know the department number of the sales department.
SELECT ENAME FROM EMP LEFT JOIN DEPT USING (DEPTNO) WHERE DNAME = 'SALES'
9. List all employees whose salary is higher than the average salary of the company.
SELECT * FROM EMP WHERE SAL > (SELECT AVG (SAL) FROM EMP)
-10. List all employees who do the same job as "SCOTT".
SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SCOTT') AND ENAME' SCOTT'
11. List the names and salaries of all employees whose salary is equal to that of the employees in the department 30.
SELECT ENAME,SAL FROM EMP WHERE SAL = ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO 30
12. List the names and salaries of employees whose salaries are higher than those of all employees working in department 30.
SELECT ENAME,SAL FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO
30
13. List the number of employees working in each department, average salary and average length of service.
Number of SELECT DEPTNO,COUNT (EMPNO) employees, ROUND (AVG (SAL), 2) average salary, ROUND (AVG (MONTHS_BETWEEN (CURRENT_DATE,HIREDATE)) / 12 Magi 2) average length of service FROM EMP RIGHT JOIN DEPT USING (DEPTNO) GROUP BY DEPTNO
14. List the names, department names and salaries of all employees.
SELECT ENAME,DNAME,SAL FROM EMP LEFT JOIN DEPT USING (DEPTNO)
15. List the details of all departments and the number of departments.
SELECT DEPT.DEPTNO,DNAME,LOC,COUNT (EMPNO) FROMDEPT LEFT JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNOGROUP BY DEPT.DEPTNO,DNAME,LOC
16. List the minimum wage for all kinds of jobs.
SELECT JOB,MIN (SAL) FROM EMP GROUP BY JOB
-- 17. List the minimum salary of MANAGER (managers) in each department.
SELECT MIN (SAL) FROM EMP WHERE JOB = 'MANAGER'
18. List the annual salaries of all employees, sorted from lowest to highest.
SELECT ENAME,SAL*12+NVL (COMM,0) Annual salary FROM EMP ORDER BY Annual salary
19. List the name of the manager.
SELECT ENAME FROM EMP WHERE JOB = 'MANAGER' OR JOB =' PRESIDENT'
-- 20. Find the maximum salary without a group function.
SELECT SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM = 1
21. List the employees with the second to eighth highest salary.
SELECT SAL FROM (SELECT ROWNUM RN,SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC)) WHERE RN > = 2 AND RN
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 277
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.