Simple query cases in SQL Server

/*
* Name: Human Resource Management System database
*/

/* Switch to System database master;*/
Use master;
GO

/* If an HR table exists in the current system, delete it;
IF EXISTS (SELECT 1 from sysdatabases WHERE name = ' HR ')
BEGIN
DROP DATABASE HR;
END
GO

--Create DATABASE HR
Create DATABASE HR
Go
/* Switch to HR database */
Use HR;
GO
-----------------------------------------------------------------------
--Create a table in the HR database---------1 Job Information table jobs
-----------------------------------------------------------------------
/*
* Table name: Job Information table
*/
CREATE TABLE JOBS
(
--Job number
Job_idvarchar () not NULL,--position name, primary key
Job_titlenvarchar () not NULL,--minimum wage, not less than city minimum wage 1000
Min_salarymoney not NULL,--highest salary, not less than minimum wage
Max_salarymoney not NULL,
);
GO

--Position PRIMARY KEY constraint
ALTER TABLE JOBS
ADD CONSTRAINT job_id_pk PRIMARY KEY (job_id);
--Minimum Salary CHECK constraint
ALTER TABLE JOBS
ADD CONSTRAINT job_min_salary_ck CHECK (min_salary >= 1000);
--the most high-paying check constraint
ALTER TABLE JOBS
ADD CONSTRAINT job_max_salary_ck CHECK (max_salary >= min_salary);
GO

--Test data and add multiple data at the same time (union ALL)
INSERT into JOBS (job_id, Job_title, Min_salary, Max_salary)
SELECT ' Ad_pres ', ' chief executive ', 10000, 18000 UNION all
Select ' It_mgr ', ' project manager ', 8000, 12000 UNION all
Select ' It_prog ', ' Programmer ', 4000, 10000 UNION all
Select ' Mk_mgr ', ' Market manager ', 9000, 12000 UNION all
Select ' Mk_rep ', ' Market rep ', 4000, 9000;
GO

/* Query Job table information */
SELECT job_id, Job_title, Min_salary, max_salary
From JOBS
Go

----------------------------------------------------
--Create a table in the HR database---------2-part information table departments
----------------------------------------------------
/*
* Table Name: Department Information table
*/
CREATE TABLE Departments
(
--Department Number primary key IDENTITY (1), each automatic growth of 1----self-increment
Department_idint not nullidentity (),
--Department Name
Department_namenvarchar (+) not NULL,
--Department Supervisor number
Manager_idintnull,
);
GO

--Employee PRIMARY KEY constraint
ALTER TABLE Departments
ADD CONSTRAINT department_id_pk PRIMARY KEY (department_id);
GO

--Test data
/*
--The first method
INSERT into departments (Department_name, MANAGER_ID)
VALUES (' Office of the President ', NULL);
INSERT into departments (Department_name, MANAGER_ID)
VALUES (' marketing department ', NULL);
INSERT into departments (Department_name, MANAGER_ID)
VALUES (' software department ', NULL);
INSERT into departments (Department_name, MANAGER_ID)
VALUES (' Finance Department ', NULL);
GO
*/
--The second method
INSERT INTO departments (Department_name, MANAGER_ID)
Select ' President's office ', NULL UNION ALL
Select ' Marketing department ', NULL UNION ALL
Select ' Software department ', NULL UNION ALL
Select ' Finance Department ', NULL
Go

/* Query Department table information */
SELECT department_id, Department_name, manager_id
From departments;
GO

----------------------------------------------------
--Create a table in the HR database---------3 Employee Information Sheet Employees
----------------------------------------------------

/*
* Table name: Employee Information Table
* Description: When updating the department number or position number for this table, add data to the Job_history table and update the entry time to the current date.
*/
CREATE TABLE EMPLOYEES
(
--Staff number
Employee_idintnot nullidentity (100,1),
--staff name
First_ Namenvarchar (4) NOT NULL,
--staff Last name
Last_namenvarchar (4) NOT NULL,
--Staff mail
Emailvarchar (+) NOT NULL,
-- Staff phone
Phone_numbervarchar not NULL,
--Entry time
Hire_datedatetimenot NULL,
--Staff position number
Job_idvarchar (10 ) NOT NULL,
--staff salary
Salarymoney NULL,
--employee's superior staff number
Manager_idintnull,
--employee's department number
Department_ Idintnull,
);
GO

--Employee's PRIMARY KEY constraint
ALTER TABLE EMPLOYEES
ADD CONSTRAINT employee_id_pk PRIMARY KEY (employee_id);
--Employee's entry time default value
ALTER TABLE EMPLOYEES
ADD CONSTRAINT employee_hire_date_df DEFAULT (getdate ()) for hire_date;
--inspection and restraint of staff salaries
ALTER TABLE EMPLOYEES
ADD CONSTRAINT employee_salary_ck CHECK (SALARY > 0);
--The FOREIGN KEY constraint of the employee's superior (many-to-one relationship between staff and staff [manager])
ALTER TABLE EMPLOYEES
ADD CONSTRAINT employee_manager_id_fk FOREIGN KEY (manager_id) REFERENCES EMPLOYEES (employee_id);
--A FOREIGN KEY constraint for a staff position (a many-to-one relationship between staff and positions)
ALTER TABLE EMPLOYEES
ADD CONSTRAINT employee_job_id_fk FOREIGN KEY (job_id) REFERENCES JOBS (job_id);

--FOREIGN KEY constraints for departmental managers (one-to-one relationships between departments and employees)
ALTER TABLE Departments
ADD CONSTRAINT department_employee_manager_id_fk FOREIGN KEY (manager_id) REFERENCES EMPLOYEES (employee_id);
--FOREIGN KEY constraints in the employee department (many-to-one relationship between staff and departments)
ALTER TABLE EMPLOYEES
ADD CONSTRAINT employee_department_id_fk FOREIGN KEY (department_id) REFERENCES departments (DEPARTMENT_ID);
GO

/* NEW President */
--New president
Insert into EMPLOYEES (first_name, last_name, EMAIL, Phone_number, job_id, SALARY, manager_id, department_id)
VALUES (' Cloud ', ' horse ', ' [email protected] ', ' 023-68887888 ', ' ad_pres ', 12000, NULL, 1);
--Get the identity column;
SELECT @ @IDENTITY from EMPLOYEES;
--The head of department responsible for revising the department table;
UPDATE departments SET manager_id = WHERE department_id = 1;

--New Marketing department manager
Insert into EMPLOYEES (first_name, last_name, EMAIL, Phone_number, job_id, SALARY, manager_id, department_id)
VALUES (' All ', ' Guo ', ' [email protected] ', ' 023-68881666 ', ' mk_mgr ', 10000, NULL, 2);
--Get the identity column;
SELECT @ @IDENTITY from EMPLOYEES;
--The head of department responsible for revising the department table;
UPDATE departments SET manager_id = 101 WHERE department_id = 2

--New software department manager
Insert into EMPLOYEES (first_name, last_name, EMAIL, Phone_number, job_id, SALARY, manager_id, department_id)
VALUES (' protected ', ' lid ', ' [email] ', ' 023-68881888 ', ' it_mgr ', 10000, NULL, 3);
--Get the identity column;
SELECT @ @IDENTITY from EMPLOYEES;
--The head of department responsible for revising the department table;
UPDATE departments SET manager_id = 102 WHERE department_id = 3

--New programmer
Insert into EMPLOYEES (first_name, last_name, EMAIL, Phone_number, job_id, SALARY, manager_id, department_id)
VALUES (' read ', ' Deng ', ' [email protected] ', ' 023-67771234 ', ' It_prog ', 5000, 102, 3);

Insert into EMPLOYEES (first_name, last_name, EMAIL, Phone_number, job_id, SALARY, manager_id, department_id)
VALUES (' strong ', ' Zhang ', ' [email protected] ', ' 023-67771235 ', ' It_prog ', 5500, 102, 3);

Insert into EMPLOYEES (first_name, last_name, EMAIL, Phone_number, job_id, SALARY, manager_id, department_id)
VALUES (' Long Hai ', ' Wu ', ' [email protected] ', ' 023-67771236 ', ' It_prog ', 4500, 102, 3);

/* Query Staff table information */
SELECT employee_id, first_name, last_name, EMAIL, Phone_number, Hire_date, job_id,
SALARY, manager_id, department_id
From EMPLOYEES
GO

----------------------------------------------------
--Create a table in the HR database---------4 job History table Job_history
----------------------------------------------------

/*
* Table Name: Employment History Table
* Note: When updating the department number or job number of the Employees table, you should add data to this table.
*/
CREATE TABLE Job_history
(
--Employee number
Employee_idintnot NULL,
--Start time of appointment
Start_datedatetimenot NULL,
--end-of-Office time
End_datedatetimenot NULL,
--Job number
Job_idvarchar (Ten) is not NULL,
--Number of office departments
Department_idintnull,
);
GO

--Job History PRIMARY KEY constraint (combination primary key for employee number and start time)
ALTER TABLE Job_history
ADD CONSTRAINT job_history_employee_id_start_date_pk PRIMARY KEY (employee_id, start_date);
--End time DEFAULT constraint
ALTER TABLE Job_history
ADD CONSTRAINT job_history_end_date_df DEFAULT (GETDATE ()) for end_date
--FOREIGN KEY constraint on the employee number (job history and many-to-one relationships between employees)
ALTER TABLE Job_history
ADD CONSTRAINT job_history_employee_id_fk FOREIGN KEY (employee_id) REFERENCES EMPLOYEES (employee_id);
--FOREIGN KEY constraint on position number (job history and many-to-one relationship between positions)
ALTER TABLE Job_history
ADD CONSTRAINT job_history_job_id_fk FOREIGN KEY (job_id) REFERENCES JOBS (job_id);
--FOREIGN KEY constraint on the department number (Job history and inter-departmental relationship)
ALTER TABLE Job_history
ADD CONSTRAINT job_history_department_id_fk FOREIGN KEY (department_id) REFERENCES departments (DEPARTMENT_ID);
GO

-Added historical data to the record of employment;
INSERT into Job_history (employee_id, start_date, end_date, job_id, department_id)
VALUES (103, ' 2011-05-06 ', DEFAULT, ' It_prog ', 3);
GO

/* Query Staff table information */
SELECT employee_id, first_name, last_name, EMAIL, Phone_number, Hire_date, job_id,
SALARY, manager_id, department_id
From EMPLOYEES
GO

/* Query Job history information */
SELECT employee_id, Start_date, end_date, job_id, department_id
From Job_history;
GO

--Query all the information in the Employees table
SELECT * FROM EMPLOYEES
Go

-----------------------------Job---------------------------------------
-----1 Job Information Table jobs
-----2-Part information sheet departments
-----3 Staff Information Sheet employees
-----4 Appointment History Table Job_history
--One, select clause exercises:
--1) To inquire all the information of the staff table;
SELECT * FROM EMPLOYEES
Go
--2) query employee number, employee name, e-mail, telephone number in the staff table;
Select Employee_id,first_name,email,phone_number from EMPLOYEES
Go
--3) query employee's staff number, employee name, and employee's annual salary
Select Employee_id,first_name,salary from EMPLOYEES
Go
--4) Query the employee's table for employee number, employee name, entry time, salary, and display the column name in Chinese; as name (Specify a new alias for the column)
Select employee_id as staff number, first_name as staff name, hire_date as entry time, SALARY as salary from EMPLOYEES
Go
--5) from the staff table, query the company's existing departments, give the number of these departments; distinct---Remove the same line department_id<>1 except for the department's number 1.
Select Department_id,department_name from departments where department_id on (select DISTINCT (department_id) from EMPLOYEES where department_id<>1)
Go

--6) from the Job history table, check the number of all staff members who have department or position changes; (optional)

--7) to inquire the top 2 employees in the staff table for information,
Select Top 2 * from EMPLOYEES
Go

--Two, use of aggregate functions:
----------------------------------------------
-----1 Job Information Table Jobs
-----2 Department Information sheet Departments
-----3 Staff Information Sheet Employees
-----4 Job History table Job_history
--1) Query the total number of staff;----as staff total number of aliases
Select COUNT (employee_ ID) as the total number of employees from EMPLOYEES
Go
--2) query The total number of department number 3rd;

Select COUNT (department_id) from EMPLOYEES where department_id=3
Go
--3) Query the total number of departments in the department table;
--select * FROM Departments
Select COUNT (department_id) from departments where department_id<>1
Go
--4) The number of departments with department heads in the enquiry Department table;

Select COUNT (manager_id) number of departments from departments where manager_id are NOT null
Go
--5) The total number of employees who have had a position or department change in the Service History table;
--select * from Job_history
Select COUNT (employee_id) from Job_history where job_id are not null and department_id are NOT null
Go
--6) query The staff table for the highest and lowest salaries;
--select * from EMPLOYEES
Select MAX (SALARY) as the highest paid, Min (SALARY) as minimum wage from EMPLOYEES
Go
--7) Query The staff table, the total number of payroll in Department 3rd, and the average salary;
Select COUNT (SALARY) as Payroll total, AVG (SALARY) as salary average from EMPLOYEES where department_id=3

--third, the use of the WHERE clause:
----------------------------------------------
-----1 Job Information Table jobs
-----2-Part information sheet departments
-----3 Staff Information Sheet employees
-----4 Appointment History Table Job_history
--1) staff information for staff number 101 is queried;
SELECT * from EMPLOYEES where employee_id= ' 101 '
Go
--2) to inquire staff information for the entry time in 2008 or prior;
--select * from EMPLOYEES
SELECT * FROM EMPLOYEES where hire_date<=2008
Go
--3) enquiries about employees with salary of 10000 yuan or above;
SELECT * FROM EMPLOYEES where salary>=10000
Go
--4) query employee information for salaries between 2500 and 7000;
SELECT * from EMPLOYEES where SALARY between 2500 and 7000
Go
--5) query Employee information for 1998 entry;
SELECT * FROM EMPLOYEES where hire_date=1998
Go
--6) Query the job number is St_clerk or St_man employees;
SELECT * from EMPLOYEES where job_id= ' St_clerk ' and job_id= ' St_man '
Go
--7) Query the staff information of surname Zhang or surname Li;
SELECT * from EMPLOYEES where last_name= ' or last_name= ' li '
Go
--8) Query the staff information of the department number starting with MK;
SELECT * from EMPLOYEES where job_id like ' mk% '
--9) search job number containing MGR position information;
SELECT * from EMPLOYEES where job_id like '%mgr% '
--10) staff information in the staff email is included with both O and U;
SELECT * from EMPLOYEES where EMAIL like '%o%u% '
Go
--11) Query staff information in the employee's email containing o or u;
SELECT * from EMPLOYEES where email like '%o% ' or email like '%u% '
Go

--12) in the Staff Mail, the 3rd character is N, and the 4th character is the employee information of G;
SELECT * from EMPLOYEES where EMAIL like ' __ng% '
Go
--13) Query The job number contains _m employee information;
SELECT * from EMPLOYEES where job_id like '%_m% '
--14) Query The Department table, which departments do not have department heads;
Select department_name,manager_id from departments where manager_id is null
--15) Query The staff table, the name of the department head and the job number; job_id
------------------------------------------------------------------------
Select Department_name as department head name from departments where department_id in (select department_id from EMPLOYEES)

Select job_id from EMPLOYEES
-------------------------------------------------------------------------
- -16) Query employee information that is not in 2010;
Select * from EMPLOYEES where hire_date<>2010
Go
--17) query for employee information with department number 3 and salary at 5000 or above
Select * from EMPLOYEES where department_id=3 or salary>=5000
Go
--18) query for employee information after 2010 years or under 5000 salary;
SELECT * from EMPLOYEES where hire_date>2010 and salary<5000
Go

--Iv. use of the ORDER BY clause: sort ORDER BY ASC DESC
----------------------------------------------
-----1 Job Information Table jobs
-----2-Part information sheet departments
-----3 Staff Information Sheet employees
-----4 Appointment History Table Job_history
--1) Query staff number, employee name, employee salary, and according to the salary descending order;
Select Employee_id,last_name+first_name,salary from EMPLOYEES ORDER by SALARY Desc
Go
--2) Check the employee number, employee name, employee entry time and order in ascending time;
Select Employee_id,last_name+first_name,hire_date from EMPLOYEES ORDER by hire_date ASC
Go
--3) query staff number, staff name, employee entry time, department number, first by department number ascending, and then by the time of entry in ascending order;
Select Employee_id,last_name+first_name,hire_date, department_id from EMPLOYEES ORDER by department_id,hire_date ASC
Go
--4) query salary ranking in the top 2 employee information;
Select Top 2 * from EMPLOYEES ORDER BY SALARY Desc
Go

--5) Randomly extracts two staff members from the staff table, listing their employee number, employee name; newid () random function
Select top 2 employee_id as staff number, last_name+first_name as staff name from EMPLOYEES Order by NEWID ()
Go

--Five, use of the GROUP by clause:
----------------------------------------------
-----1 Job Information Table Jobs
----- 2 Department Information table Departments
-----3 Staff Information Sheet Employees
-----4 Job History table Job_history
--1) query the staff table, category by department;
Select Department_id,department_name from departments where department_id on (select department_id from EMPLOYEES GROUP by depart ment_id)
Select department_id from EMPLOYEES GROUP by department_id
--2) query the staff table, by department, average salary per department;
Select AVG ( SALARY) as average salary, department_id from EMPLOYEES Group by department_id
--3) Query staff table, by job category, the highest salary for each position;
Select MAX (SALAR Y) The highest-paid EMPLOYEES group by job_id
--4) Query staff table, by department, the number of employees who pay more than 12000;
---SELECT * from EMPLOYEES
Select Coun T (SALARY) Number of staff from EMPLOYEES where salary>=12000 GROUP by department_id
--6, HAVING clause use:

--1) Query The staff table, the average salary of 7500 or more of the department number and average salary;
Select department_id department number, AVG (SALARY) Average salary from EMPLOYEES GROUP by DEPARTMENT_ID have avg (SALARY) >=7500
--2) Check the staff table, the minimum wage of 8000 or more of the department number and minimum salary;
Select department_id department number, min (SALARY) minimum salary from EMPLOYEES GROUP by department_id have MIN (SALARY) >8000
Go

--Vi. use of the INTO clause:
----------------------------------------------
-----1 Job Information Table jobs
-----2-Part information sheet departments
-----3 Staff Information Sheet employees
-----4 Appointment History Table Job_history
--1) Back up the data from the Employees table to emp_temp;
SELECT * Into Emp_temp from EMPLOYEES
--select * from Emp_temp
--2) Randomly extracts two staff members from the staff list, listing their employee number and employee name;
Select top 2 employee_id as staff number, last_name+first_name as staff name from EMPLOYEES Order by NEWID ()

Simple query cases in SQL Server