Unit 12 `T-SQL` Programming

Unit 12 `T-SQL` Programming

create database step2_unit12;
 go
 use step2_unit12;
 go
 -- Department table
 CREATE TABLE [dbo].[Department](
     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
     [Name] [varchar](20) NULL
 );
 -- Position list
 CREATE TABLE [dbo].[Job](
     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
     [Name] [varchar](20) NULL
 );
 -- Personnel table
 CREATE TABLE [dbo].[person](
     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
     [Name] [varchar](50) NULL,
     [Sex] [varchar](10) NULL,
     [Age] [int] NULL
 );
 -- Salary table
 CREATE TABLE [dbo].[Salary](
     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
     [Sal] [money] NULL,
     [Comm] [money] NULL,
     [StaffId] [int] NULL
 );
 -- Employee table
 CREATE TABLE [dbo].[Staff](
     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
     [Name] [varchar](20) NOT NULL,
     [Hiredate] [datetime] NOT NULL,
     [DepartmentId] [int] NULL,
     [JobId] [int] NULL
 );
 SET IDENTITY_INSERT [dbo].[Department] ON
 ​
 INSERT [dbo].[Department] ([Id], [Name]) VALUES (1, N'Technical Department')
 INSERT [dbo].[Department] ([Id], [Name]) VALUES (2, N'Human Resources Department')
 INSERT [dbo].[Department] ([Id], [Name]) VALUES (3, N'Marketing Department')
 SET IDENTITY_INSERT [dbo].[Department] OFF
 SET IDENTITY_INSERT [dbo].[Job] ON
 ​
 INSERT [dbo].[Job] ([Id], [Name]) VALUES (1, N'Manager')
 INSERT [dbo].[Job] ([Id], [Name]) VALUES (2, N'Team leader')
 INSERT [dbo].[Job] ([Id], [Name]) VALUES (3, N'Employees')
 SET IDENTITY_INSERT [dbo].[Job] OFF
 SET IDENTITY_INSERT[dbo].[person] ON
 ​
 INSERT [dbo].[person] ([Id], [Name], [Sex], [Age]) VALUES (  1, N'  Gao Yuanyuan', N  'Female', 28)
 INSERT [dbo].[person] ([Id], [Name], [Sex], [Age]) VALUES (  2, N'  Wang Jiajia', N  'Female', 32)
 INSERT [dbo].[person] ([Id], [Name], [Sex], [Age]) VALUES (  3, N'  Yang Shuo', N'Male'  , 25)
 SET IDENTITY_INSERT [dbo].[person] OFF
 SET IDENTITY_INSERT [dbo].[Salary] ON
 ​
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  1, 15000.0000,   10000.0000, 1)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  2, 15000.0000,   9000.0000, 2)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  3, 12000.0000,   8000.0000, 3)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  4, 8000.0000,   6000.0000, 4)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  5, 4000.0000,   3000.0000, 5)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  6, 9000.0000,   8000.0000, 6)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  7, 15000.0000,   12000.0000, 7)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  8, 12000.0000,   9000.0000, 7)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  9, 7000.0000,   2000.0000, 9)
 INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (  10, 9000.0000,   8000.0000, 10)
 SET IDENTITY_INSERT [dbo].[Salary] OFF
 SET IDENTITY_INSERT [dbo].[Staff] ON
 ​
 INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (1, N'Wang Xin', CAST(N'2014-02-01 00:00:00.000' AS DateTime), 1, 1)
 INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (2, N'Zhang Lei', CAST(N'2015-09-03 00:00:00.000' AS DateTime), 2, 1)
 INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (3, N'Li Peng', CAST(N'2016-08-01 00:00:00.000' AS DateTime), 3, 2)
 INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (4, N'Wang Yang', CAST(N'2019-02-03 00:00:00.000' AS DateTime), 1, 3)
 INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (5, N'Wu Pan', CAST(N'2018-10-01 00:00:00.000' AS DateTime), 2, 3)
 INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (6, N'Jia Dan', CAST(N'2018-07-01 00:00:00.000' AS DateTime), 2, 2)
 INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (7, N'Li Lu', CAST(N'2016-09-01 00:00:00.000' AS DateTime), 3, 1)
 INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (8, N'Yang Teng', CAST(N'2018-11-08 00:00:00.000' AS DateTime), 1, 2;
 --declare @age2 int;
 --declare @sum int;
 select @age1 = Age from person where Name='Gao Yuanyuan  ';
 select @age2 = Age from person where Name='Wang Jiajia  ';


 set @sum=@age1+@age2;
 --select @sum=@age1+@age2;
 select @sum;
  

select Name,'VIP' as level from  person where Name='Gao Yuanyuan'

 select Name+' VIP' as VIP from person where Name='Gao Yuanyuan'
  

4. Query the information of persons older than 25 years old

   Select * from person where   age>25

5. Query the information of people who are older than 25 years old or whose gender is female

   Select * from person where   age>25 or Sex='Female  '

6. Homework

1. T-SQL programming to achieve the result of 123%7

2. Question 2

   T-sql statement is programmed as follows

   (1) Define an integer variable age

   (2) Assign the value of 10 to the variable age

   (3) Define a variable name of no more than 8 characters

   (4) Assign the name variable to “Zhang San”

   (5) Call variables and output the final result. Zhang San’s age is 10 years old

Supporting video link: SQL Server Advanced Programming – NetEase Cloud Classroom (163.com)

The sea is broad and the fish are leaping, the sky is high and I can do whatever I want. Give me a piece of blue sky and let me fly freely.