018. Query exercises 50 questions (SQL Instance)

CREATE TABLE EMP
(
EMPNO Numeric (5,0) not NULL primary key,--employee's number
ename nvarchar (ten) NOT NULL,--employee's name
Job nvarchar (9) NOT null,--position of the employee
MGR Numeric (5,0),--Supervisor Number
HireDate datetime,--Entry (Employment) date
SAL Numeric (7, 2),--salary;
COMM Numeric (7, 2),--Commission;
DEPTNO Numeric (2,0)--Department number
)

CREATE TABLE DEPT
(
DEPTNO Numeric (2) primary key,--Department number
Dname nvarchar (+) NOT NULL,--department name
LOC Department location nvarchar (13) – Department location
);
INSERT into EMP VALUES (7369, ' SMITH ', ' clerk ', 7902, ' 2000-12-17 ', +, NULL, 20);
INSERT into EMP VALUES (7499, ' Allen ', ' salesman ', 7698, ' 2001-2-20 ', 1600, 300, 30);
INSERT into EMP VALUES (7521, ' WARD ', ' salesman ', 7698, ' 2001-2-22 ', 1250, 500, 30);
INSERT into EMP VALUES (7566, ' JONES ', ' MANAGER ', 7839, ' 2001-4-2 ', 2975, NULL, 20);
INSERT into EMP VALUES (7654, ' MARTIN ', ' salesman ', 7698, ' 2001-9-28 ', 1250, 1400, 30);
INSERT into EMP VALUES (7698, ' BLAKE ', ' MANAGER ', 7839, ' 2001-5-1 ', 2850, NULL, 30);
INSERT into EMP VALUES (7782, ' CLARK ', ' MANAGER ', 7839, ' 2001-6-9 ', 2450, NULL, 10);
INSERT into EMP VALUES (7788, ' Scott ', ' ANALYST ', 7566, ' 2002-12-9 ', +, NULL, 20);
INSERT into EMP VALUES (7839, ' king ', ' president ', NULL, ' 2001-11-17 ', n, NULL, 10);
INSERT into EMP VALUES (7844, ' TURNER ', ' salesman ', 7698, ' 2001-9-8 ', 1500, 0, 30);
INSERT into EMP VALUES (7876, ' ADAMS ', ' clerk ', 7788, ' 2003-1-12 ', 1100, NULL, 20);
INSERT into EMP VALUES (7900, ' JAMES ', ' clerk ', 7698, ' 2001-3-12 ', 950, NULL, 30);
INSERT into EMP VALUES (7902, ' FORD ', ' ANALYST ', 7566, ' 2001-3-12 ', +, NULL, 20);
INSERT into EMP VALUES (7934, ' MILLER ', ' clerk ', 7782, ' 2002-01-23 ', 1300, NULL, 10);
INSERT into DEPT VALUES (' ACCOUNTING ', ' NEW YORK ');
INSERT into DEPT VALUES ("DALLAS");
INSERT into DEPT VALUES (' SALES ', ' CHICAGO ');
INSERT into DEPT VALUES (+, ' OPERATIONS ', ' BOSTON ');
SELECT *from EMP

--1, query all employee names
SELECT ename from EMP

--2, query all departments
SELECT DISTINCT JOB from EMP--distinct hide duplicate rows

--3, query all employee information without Commission (COMM)
SELECT * from EMP
WHERE COMM is NULL

--4, Query Salary (SAL) and Commission (COMM) totals greater than 2000 of all employee information
SELECT * from EMP
WHERE sal+isnull (comm,0) >2000
Tip: IsNull (column name, 0): If there is a null value in the column, the null value is calculated as 0
--5, select the employee in the department number =30
SELECT * from DEPT
WHERE deptno=30

--6, lists the name, number, and department name of all job Clerks ("clerk")
SELECT Ename,empno,dname from Emp,dept
WHERE EMP. Deptno=dept. DEPTNO and job= ' clerk '

--7, finding employees with higher commissions than salaries
SELECT ename from EMP
WHERE Comm>sal

--8, finding 60% of employees with commissions above their salaries
SELECT ename from EMP
WHERE comm>0.6 * SAL
--9, find out the details of all the clerks in department 10, all managers and departments 20
SELECT *from EMP
WHERE job= ' MANAGER ' and deptno=10 OR job= ' clerk ' and DEPTNO =20

--10 details of all employees who are neither managers nor clerks but whose salaries >=2000
SELECT *from EMP
WHERE job!= ' MANAGER ' and sal>=2000 OR job!= ' clerk ' and sal>=2000

--11, find out the different jobs of employees who receive commissions
SELECT DISTINCT JOB from EMP
WHERE COMM is not NULL

--12, find employees who do not receive commissions or receive commissions less than 100
SELECT ename from EMP
WHERE COMM is NULL OR comm<100

--13, identify employees employed before 8 years
SELECT ename from EMP
WHERE GETDATE ()-hiredate>8

--14, name of all employees with first letter capitalization
--Case Sensitivity: Collate: Specifies the collation of the
--Modify table, set case sensitivity, chinese_prc_ci_as case insensitive
--Case Sensitive chinese_prc_cs_as
ALTER TABLE EMP
ALTER column ename varchar (20)
Collate chinese_prc_cs_as

Select ename from emp
where ename like ' [a]% '

--15, displaying an employee name that is exactly 5 characters
SELECT ename from EMP
WHERE ename like ' _____ '

--16, displaying employee names with ' R ' are not case-sensitive
SELECT ename from EMP
WHERE ename like '%r% '

--17, show employee names without ' R '
SELECT ename from EMP
WHERE ename not like '%r% '

--18, displays the names of all employees that contain "a" and the position of "a" in the Last Name field
--Hint: Use function charindex ()
SELECT ename,charindex (' A ', ename) from EMP
WHERE ename like '%a% '

--19, display the names of all employees, replace all ' a ' with a
Tip: Replace ()
SELECT ename,replace (ename, ' a ', ' a ') from EMP



--20, showing the first three characters of all employees ' names
Tip: Left ()
SELECT Ename,left (ename,3) from EMP

--21, show employee details, sort by name
--order by sort
SELECT * from EMP
ORDER by ename

--22, showing the employee's name, according to their service life, the oldest employees in the front
SELECT Ename,hiredate from EMP
ORDER by HireDate ASC

--23, displays the names, jobs, and salaries of all employees, sorted by job descending order,
--and work the same in ascending order by salary.
SELECT Ename,job,sal from EMP
ORDER BY JOB Desc,sal ASC--If the first sort condition repeats, continue with the second sort

--24, showing the daily salary of all employees in the case of one months for 30 days, ignoring decimals
SELECT Ename,sal/30, CAST (Sal/30 as INT) from EMP
--cast (xx as conversion to what type)

--25, identify all employees employed in February (in any year)
Hint: month () takes months, year () gets years, day () takes days, DATEPART () gets a part of the date?
SELECT Ename,hiredate from EMP
WHERE MONTH (hiredate) =2


--26, for each employee, shows the number of days they joined the company
Tip: DateDiff (day,hiredate,getdate ())
Gets the difference of two time. (Unit optional)
SELECT ename, DATEDIFF (Day,hiredate,getdate ()) from EMP

--27, list all departments that have at least one employee
SELECT Deptno,count (EMPNO) number from EMP
GROUP by DEPTNO
Have COUNT (EMPNO) is not NULL

--28, list minimum wage for various categories of work
SELECT job,min (Sal+isnull (comm,0)) minimum wage from EMP
GROUP by JOB

--29, list the manager's minimum salary for each department

SELECT DEPTNO Department name, Min (SAL) Minimum salary from EMP
WHERE job= ' MANAGER '
GROUP by DEPTNO

--30, list all employees with salary above the company's average level
SELECT ename employee name from EMP
WHERE sal> (SELECT AVG (SAL) from EMP)

--31, lists minimum salaries for various job categories and shows minimum salary of more than 1500
SELECT job,min (SAL) from EMP
GROUP by JOB
Having MIN (SAL) >1500

--32, displays the names of all employees and the year and month in which the company was joined, sorted by the month of the employee's employment date, and ranked the first year of the project $$$$$$$$$$$$$$$
SELECT Ename,month (HireDate), year (hiredate) from EMP
ORDER by MONTH (HireDate), year (HireDate) ASC

--33, showing the names of all employees and the dates of service years of 8 years
SELECT Ename,hiredate from EMP
WHERE DATEDIFF (Year,hiredate,getdate ()) >=8
--datediff (Year/month/day, field, GETDATE ()) Time difference value

--34, showing the service life of all employees: the total number of years or the total number of months or the total number of days
SELECT ename as name, DATEDIFF (Year,hiredate, GETDATE ()) as number of working years,
DATEDIFF (Month,hiredate,getdate ()) as working months, DATEDIFF (Day,hiredate,getdate ()) as working days
From EMP

--35, lists the annual salary of all employees sorted by calculated field. That is, the employees are sorted according to their annual salary, and the annual salary refers to the total monthly income of the employee total 12 months cumulative
SELECT ename, sal*12 from EMP
ORDER by Sal*12 ASC

--36, List of the top 5 employees
SELECT TOP 5 ename, sal*12 as annual salary from EMP
ORDER by Sal*12 DESC

--37, List of employees with an annual salary of less than 10000
SELECT ename, sal*12 as annual salary from EMP
WHERE sal*12<10000

--38, List employees ' average monthly salary and average annual salary
SELECT CAST (AVG (SAL) as int) as average monthly salary, CAST (avg (sal*12) as int) as average annual salary from EMP

--39, List department names and employees of these departments, and list the departments with no employees
SELECT dname,ename from EMP right JOIN DEPT
On EMP. Deptno=dept. DEPTNO

--40, list information for each department and number of employees in the department
SELECT DEPT. Deptno,dname,loc department location, COUNT (EMPNO) number of employees from the EMP right JOIN DEPT
On EMP. Deptno=dept. DEPTNO
GROUP BY Dname,loc Department location, DEPT. DEPTNO

--41, list all employees with more than "SMITH" salary
SELECT * from EMP
SELECT a.ename,a.sal from EMP as a,emp as B
WHERE a.sal>b.sal and B.ename= ' SMITH '--self-joins when the values of the same column are compared to each other
ORDER by A.sal ASC

--42, lists the names of all employees and their immediate superiors (self-connect) $$$$$$$$$$$$$$$$$$$$$$$$$
--Self-connection: it is a table that connects itself to itself,
--Be sure to use table aliases to differentiate them
--EG1:
SELECT A.empno as employee, A.ename,a.mgr,b.empno,b.ename as Superior
From EMP A,emp b
WHERE A.mgr=b.empno

--EG2:
SELECT A.empno as employee, B.ename as Superior
From EMP A,emp b
WHERE A.mgr=b.empno

--EG3:
SELECT A.ename employee, B.ename as Superior
From EMP a LEFT join EMP b
On A.mgr=b.empno

--43, List all employees whose entry date is earlier than their immediate superiors $$$$$$$$$$$$$$$$$$$$$$$$$
SELECT a.ename from EMP as a,emp as B
WHERE A.mgr=b.empno and A.hiredate<b.hiredate

--44, List Names of all clerks ("clerk") and their department names
SELECT * from Emp,dept
--Query with inner connection
--EG1:
SELECT EMP. Ename,dept. Dname from EMP JOIN DEPT
On EMP. job= ' clerk ' and EMP. Deptno=dept. DEPTNO
--EG2:
SELECT Ename,dname from Emp,dept
WHERE EMP. Deptno=dept. DEPTNO and EMP. job= ' clerk '

--45 the name of the employee who is engaged in the sales job, assuming that the department number of the sales department is not known
--EG1:
SELECT EMP. Ename from EMP JOIN DEPT
On EMP. Deptno=dept. DEPTNO and Dname= ' SALES '
--EG2:
SELECT ename from Emp,dept
WHERE EMP. Deptno=dept. DEPTNO and DEPT. Dname= ' SALES '

--46, list all employees who work in the same way as "SCOTT"
--select DISTINCT EMP. Ename from EMP left JOIN DEPT
--on EMP. Deptno=dept. DEPTNO

--select DISTINCT * from EMP
SELECT b.ename from EMP as A JOIN EMP as B
On A.deptno=b.deptno and A.ename= ' SCOTT ' and b.ename!=a.ename
JOIN DEPT
On DEPT. Deptno=a.deptno

--47, list the names and salaries of certain employees, provided that their salaries are equal to the salary of any employee in Department 30

--48, list the names and salaries of certain employees, provided that their salaries are higher than the salaries of all employees in department 30

--49, list of different combinations of employees engaged in the same job but belonging to different departments
SELECT DISTINCT a.ename, b.ename from EMP as a,emp as B
WHERE A.job=b.job and A.deptno!=b.deptno

018. Query exercises 50 questions (SQL Instance)