Basic SQL command LAB
Note: create a Table EMP_your_StudentID and DEPT_your_studentID with all required constraints both Integrity and Referential Integrity Constraint.
INSERT TUPLES FOR EMP TABLE:
INSERT TUPLES FOR DEPT TABLE:
- Create a database
Create two tables
- List all information about the employees.
--question1 SELECT * FROM emp;
- List all information about the departments
--question2 SELECT * FROM dept
- List only the following information from the EMP table ( Employee name, employee number, salary, department number)
--question3 SELECT ENAME, EMPNO, SAL, DEPTNO FROM emp;
- List details of employees in departments 10 and 30.
-- question4 SELECT * FROM emp WHERE `DEPTNO` = 10 OR `DEPTNO` = 30;
- List all the jobs in the EMP table eliminating duplicates.
-- question5 SELECT DISTINCT JOB FROM emp
- What are the names of the employees who earn less than £20,000?
-- question6 SELECT emp.ENAME FROM emp WHERE emp.SAL < 20000
- What is the name, job title and employee number of the person in department 20 who earns more than £25000?
-- question7 SELECT emp.ENAME, emp.JOB, emp.EMPNO FROM emp WHERE emp.DEPTNO = 20 AND emp.SAL > 25000;
- Find all employees whose job is either Clerk or Salesman.
-- question8 SELECT * FROM emp WHERE emp.JOB = 'CLERK' OR emp.JOB = 'SALESMAN';
- Find any Clerk who is not in department 10.
-- question9 SELECT * FROM emp WHERE emp.JOB = 'CLERK' AND emp.DEPTNO != 10;
- Find everyone whose job is Salesman and all the Analysts in department 20.
-- question10 SELECT * FROM emp WHERE emp.JOB = 'SALESMAN' OR ( emp.JOB = 'ANALYST' AND emp.DEPTNO = 20 );
- Find all the employees who earn between £15,000 and £20,000.
Show the employee name, department and salary.
-- question11 SELECT emp.ENAME, emp.DEPTNO, emp.SAL FROM emp WHERE emp.SAL BETWEEN 15000 AND 20000;
- Find the name of the President.
-- question12 SELECT emp.ENAME FROM emp WHERE emp.JOB = 'PRESIDENT';
- Find all the employees whose last names end with S
-- question13 SELECT * FROM emp WHERE emp.ENAME LIKE '%S';
- List the employees whose names have TH or LL in them
-- question14 SELECT * FROM emp WHERE emp.ENAME LIKE '%TH%' OR emp.ENAME LIKE '%LL%';
- List only those employees who receive commission.
-- question15 SELECT * FROM emp WHERE emp.COMM IS NOT NULL AND emp.COMM != 0;
- Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.
-- question16 SELECT emp.ENAME, emp.JOB, emp.SAL, emp.HIREDATE, emp.DEPTNO FROM emp ORDER BY emp.ENAME ASC;
- Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.
-- question17 SELECT emp.ENAME, emp.JOB, emp.SAL, emp.HIREDATE, emp.DEPTNO FROM emp ORDER BY emp.SAL ASC;
- List all salesmen in descending order by commission divided by their salary.
-- question18 SELECT * FROM emp WHERE emp.JOB = 'SALESMAN' ORDER BY emp.COMM / emp.SAL DESC;
- Order employees in department 30 who receive commision, in ascending order by commission
-- question19 SELECT * FROM emp WHERE emp.DEPTNO = 30 AND emp.COMM IS NOT NULL AND emp.COMM != 0 ORDER BY emp.COMM ASC;
- Find the names, jobs, salaries and commissions of all employees who do not have managers.
-- question20 SELECT emp.ENAME, emp.JOB, emp.SAL, emp.COMM FROM emp WHERE emp.MGR NOT IN ( SELECT emp.EMPNO FROM emp WHERE emp.JOB = 'MANAGER' ); -- question20 SELECT e1.ENAME, e1.JOB, e1.SAL, e1.COMM FROM emp as e1,emp as e2 WHERE e1.MGR=e2.EMPNO and e2.JOB!='MANAGER';
- Find all the salesmen in department 30 who have a salary greater than or equal to £18000.
-- question21 SELECT * FROM emp WHERE emp.DEPTNO = 30 AND emp.SAL >= 18000;
- Find the employees who were hired before 01-Jan-1998 and have salary above 5000 or below 1000.
-- question22 SELECT * FROM emp WHERE emp.HIREDATE < '1998-1-1' AND ( emp.SAL > 5000 OR emp.SAL < 1000 );
- What is the command to add primary key constraint to EMPNO
-- question23 ALTER TABLE emp ADD PRIMARY KEY ( EMPNO );
- What is the command to add a new column EMP_family_name to existing EMP table
-- question24 ALTER TABLE emp ADD family_name VARCHAR ( 10 );
- How to drop primary key constraint for EMPNO
-- question25 ALTER TABLE emp DROP PRIMARY KEY ;
- rename EMP table to EMPLOYEE
-- question26 RENAME TABLE emp TO EMPLOYEE;
- rename EMPLOYEE back to EMP
-- question27 RENAME TABLE EMPLOYEE TO emp;
- What is the SQL command to remove column EMP_family_name from EMP table
-- question28 ALTER TABLE emp DROP emp.family_name;
- What is the SQL command to copy emp table to employee table
-- question29 CREATE TABLE employee AS SELECT * FROM emp;
- What is the SQL command to drop employee table
-- question30 DROP TABLE employee;
- What is the SQL command to display name’s of employee entered interactively from user
-- question31 SELECT * FROM emp WHERE emp.ENAME LIKE '%用户输入字符串%';
- What is the SQL command to find the employee whose commission is NULL
-- question32 SELECT * FROM emp WHERE emp.COMM IS NULL;