SQL statements to create a database and some query exercises

--Create MyCompany database use Masterexecute sp_configure ' show advanced options ', 1--Open permissions Reconfigureexecute sp_configure ' xp_ Cmdshell ', 1reconfigureexecute xp_cmdshell ' mkdir e:\ Job database '--Automatically create folder execute sp_configure ' xp_cmdshell ', 0--off permissions, First off Reconfigureexecute sp_configure ' show advanced options ', 0 off, a word, first open after Reconfigureif exists (SELECT * from sysdatabases where name= ' MyCompany ') drop database mycompanygocreate database Mycompanyon primary (name= ' Mycompany_data ', SIZE=3MB, Filegrowth=10%,maxsize=100mb,filename= ' e:\ job database \mycompany_data.mdf ') Log on (name= ' Mycompany_log ', SIZE=3MB, Filegrowth=10%,maxsize=100mb,filename= ' e:\ job Database \mycompany_log.ldf ')--departmental table Department--did--dnameuse MyCompanyif Exists (SELECT * from sysobjects where name= ' department ') DROP table departmentgocreate Table department (do int not NULL PR Imary key Identity (dname), nvarchar (+) NOT NULL)--Employee table Employee--eid--ename--esex--eage--esalary--edepid--eintime Onboarding Time if exists (select * from sysobjects where name= ' employee ') drop table EMPLOYEEGOCREate table employee (eId int NOT null primary key identity (), ename nvarchar (+), Esex char (2), Eage int , esalary money, Edepid int, eintime datetime) Add foreign key Fk_employee_department_edepid

ALTER TABLE EMPLOYEEADD constraint fk_employee_department_edepid foreign key (edepid) References Department (DID) insert Into department values (' teaching Department ') insert into department values (' Business unit ') insert into department values (' Department of Engineering ') Insert int o Employee values (' Hach ', ' Male ', 28,5000,1, ' 2014-8-8 ') insert into employee values (' month ', ' female ', 28,5000,2, ' 2013-8-8 ') inser   T into employee values (' Li Dingshan ', ' Male ', 18,3000,3, ' 2014-8-1 ') insert into employee values (' Zhang San ', ' Male ', 18,1800,3, ' 2014-7-1 ') INSERT into employee values (' 31 ', ' female ', 18,1800,3, ' 2013-7-1 ') insert into employee values (' Zhang 32 ', ' female ', 28,4800,2, ' 2011-7- 1 ')--build a database to build a table constraints add test data Update and delete--1, query all employees SELECT * from Employee--2, query employees who pay more than 2000 fast Money SELECT * FROM employee where eSa   lary>2000--3, Query the latest 5 employees---descending sort select top 5 * FROM Employee ORDER by Eintime desc--4, query employee's average salary select AVG () from Select AVG (esalary) as average salary from employee--5, query total number of employees count select COUNT (*) as employees from employee--6, query how many employees in each department---by department Door Group Select Edepid,count (*) As from the employee group by edepid--7, query the average wage for each department select EDEPID,AVG (esalary) from the employee group by edepid--8, query each Average wage for male employees Select EDEPID,ESEX,AVG (esalary) from employee where esex= ' Men ' GROUP by edepid,esex--9, queries for those departments with average wage exceeding 2000 ha Ving Tsun to filter the data after grouping select EDEPID,AVG (esalary) from the employee GROUP by EDEPID have avg (esalary) >200--10, query employee wages (name, Wages), the same result set shows the average wage and the highest wage--select ' + name, salary from table--union--select cast (average wage as varchar ()), Maximum wage from table Select Ename,esalary F Rom employeeunion allselect ' average wage ', AVG (esalary) from employeeunion allselect ' maximum wage ', max (esalary) from employee--this The way is also select Ename,esalary from employeeunion allselect convert (varchar (a), AVG (esalary)), Max (esalary) from employee-- 11, Query the name contains ' fixed, month ' These employees, and the results by age sort select * from the employee where ename like '% three ' or ename like '% fixed% ' ORDER by eage

----Okay, no updates and deletions.

Update table name Set field = new assignment where field = ' '
For example: Change the name of Zhang San to amuse

Ename= ' Zhang San '

Delete table name  = = And so dismissed, followed by where otherwise completely deleted-

--Restore the value of the self-growth column to a seed when deleting data

TRUNCATE TABLE name-obsessive-compulsive disorder