Simple query cases in SQL Server

Source: Internet
Author: User

/*
* 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.