Chapter 4 Data Query 1 (Computer)

Chapter 4 database query (1) -- create database createdatabasedbSalesgo -- Use Database dbSalesusedbSalesgo -- create employee table createtabletbEmp (eIDsmallintidentityp

Chapter 4 database query (1) ========================================================== ========================================================== ====-- create database dbSales go -- use database dbSales use dbSales go -- create employee table create table tbEmp (eID smallintidentity p

Chapter 4 database query (1)

========================================================== ========================================================== ====

-- Create a database

Create database dbSales
Go
-- Use Database dbSales
Use dbSales
Go
-- Create employee table
Create table tbEmp
(
EID smallintidentity primary key, -- employee ID
EName varchar (20) not null, -- employee name
ESex varchar (2) not null -- staff gender
Check (esex in ('male', 'female ')),
EAge smallintnot null check (eage> = 18), -- employee age
EAddr varchar (50) not null, -- employee address
ETel varchar (30) not null, -- employee phone number
EEmail varchar (30) null, -- employee email
EJoinTime datetimenot null, -- entry time
)
Go
Insert into tbemp ---------------------------------------------------------
(Ename, esex, eage, eaddr, etel, eemail, ejointime)
Values
('Zhao long', 'male', 25, 'no. 203, building 9, jiangnanyuan, wujialing city, Changsha city, Hunan Province ', '2017-123', 'zl @ 163.net', '2017-10-5 ')
Insert into tbemp ---------------------------------------------------------
(Ename, esex, eage, eaddr, etel, eemail, ejointime)
Values
('Li yun', 'female ', 23, 'no. 502, building 21, Dongfeng New Village, Dongfeng Road, Changsha city, Hunan Province', '2017-0731 ', 'ly @ 163.net ', '2017-7-3 ')
Insert into tbemp ---------------------------------------------------------
(Ename, esex, eage, eaddr, etel, eemail, ejointime)
Values
('Sun cheng', 'male', 24, 'no. 601, building 15, dormitory, No. 15, Zhuzhou city, Hunan province, '308-0732', 'syc @ 163.net ', '2014-2-11 ')
Insert into tbemp ---------------------------------------------------------
(Ename, esex, eage, eaddr, etel, eemail, ejointime)
Values
('Lin xiao', 'male', 27, 'no. 301, building 20, Chenzhou People's Hospital, Hunan province, '0735-2245214', 'lx @ 163.net ', '2014-1-5 ')
Insert into tbemp ---------------------------------------------------------
(Ename, esex, eage, eaddr, etel, eemail, ejointime)
Values
('Wei qing', 'female ', 23, 'no. 403, building 12, wangyue Lake, Changsha city, Hunan Province', '192-0731 ', 'wq @ 163.net', '192-3-5 ')



========================================================== ========================================================== ====
-- Query Information of all employees
Select * from tbEmp


-- Query the names, phone numbers, and addresses of all employees
Select ename, etel, eaddr from tbEmp


-- Query detailed information of all female employees
Select * from tbemp where esex = 'femal'


-- Query the names and gender of employees between the ages of 24 and 26.
Select ename, esex from tbemp where eage between 24 and 26


-- Query the name, phone number, and address of a female employee who lives in Changsha.
Select ename, etel, eaddr from tbemp where eaddr like '% Changsha %'


-- Query the phone number and address of Li Yun, Sun Cheng, and Lin Xiao
Select ename, etel, eaddr from tbemp where ename = 'Li yun' or ename = 'Sun Yicheng 'or ename = 'lin xiao'


-- Query the names, gender, and age of employees in Chenzhou and Zhuzhou
Select ename, esex, eage from tbemp where eaddr like '% Chenzhou %' or eaddr like '% Zhuzhou %'


-- Query the names of male employees aged between 25 and 28 in Changsha.
Select ename from tbemp where eaddr like '% Changsha %' and esex = 'male' and eage between 25 and 28


-- Query employees whose email addresses are empty
Select ename from tbemp where eemail is null


-- ** Query employees who have been hired for more than two years **
Select ename from tbemp where datediff (yy, ejointime, getdate ()> 2


-- ** Identify employees who joined the company in March **
Select ename from tbemp where datepart (mm, ejointime) = 1



-- ** Change all emails 163.net to 126.com **
Update tbemp
Set eemail = replace (eemail, '2014. net', '2014. com ')


-- Identify the youngest two employees in Changsha
Select top 2 ename, eage from tbemp where eaddr like '% Changsha %' order by eage asc


-- *** Query the employee information. The result set must be expressed in the following way:
-- Field name: Personal Information
-- Description: 'employee 1, Zhao Long, 25 years old, lives at No. 203, building 9, jiangnanyuan, wujialing, Changsha city, Hunan'
Select 'employee '+ convert (char (2), eid) + ','

+ Ename + ', This year' + convert (varchar (2), eage) + 'years old, home ''+ eaddr as personal information from tbemp

-- ** Calculate the total age of all employees
Select sum (eage) 'Age sume' from tbemp


-- *** Calculate the age sum of male and female employees respectively

Select sum (eage) Age sum, esex gender from tbemp group by esex select sum (eage) from tbemp where esex = 'male' or esex = 'female'

========================================================== ========================================================== ====

2013.12.02 database Fourth Course