T-SQL Advanced query of SQL Server Database

First, the functions used by the query

Please create your own database and table, and insert data into the table. My example is as follows:

Create database accp; use accp; create table test (serial number int identity (1) not null, name nvarchar (4) not null, document number varchar (18) primary key, job nvarchar (6) null, entry time datetime null, date of birth datetime null, base salary money null check (base salary > = 0 and base salary alter table test add remarks nvarchar (50) Insert into test values ('Wang San', '11111111111111111111', '2013Accord 8pm' 1980'); insert into test values ('du Wu', '2222222222222222' intermediate operation and maintenance', '2007, 7pm, 24ft, 1999, 1999, 7pm, 8500,' veteran') Insert into test values ('Lili', '333333333333333333333333),' 20171According to the Magi Magi, '199912pencils,' 6500 'new employees'); insert into test values ('Zhao Liu', '44444444444444444444', '2016 pluses 18 races', '201618 races', 1985 '5pas', '9pas', 6000 'new employees') Insert into test values ('Wang Wu', '5555555555555555 55 55 55 5),' 2014 pedigree 9 pedigree, 1994 pedigree 11 pedigree 11 pouch 10 000 minus 10 000); insert into test values ('Li Si', '666666666666 66 66 666 6 666 66 66 66 666 6), 2018 4 pencer 16 pencils, 199612 paddles, 7500 pencils, new employees) Insert into test values ('dynasty', '7777777777777777777777 77 77 77 Insert into A values ('Liu Min', 'Renmin University of China'); insert into A values ('Sun Mingming', 'Zhejiang University'); select * from A; (name nvarchar (5) not null, professional nvarchar (10) not null,); insert into B values ('Zhang Yu', 'consultant'); insert into B values ('Liu Min', 'writer') Insert into B values ('Zhang Minghan', 'architect'); insert into B values ('Wang Bo', 'engineer'); select * from B; select A. Name A, name A, A. School, school, school. Name BBME B. Professional Bfrom A Magi B where A. Name = B. Name select A. Name A, name A, A. School, school, school. Name BBME B. Professional Bfrom An inner join B on A. Name = B. Name select A. Name A, name A, A. School, school, school. Name BBME B. Professional Bfrom A left join B on A. Name = B. Name select A. Name A, name A, A. School, school, school. Name BBME B. Professional Bfrom A right join B on A. Name = B. Name select A. Name A, name A, A. School, school, school. Name BBME B. Professional Bfrom A full join B on A. Name = B. Name use bdqn Create table products (serial number int identity (1jue 1) primary key, name nvarchar (10) not null, category nvarchar (10) not null, cost money not null check (cost = 0 and cost ('watermelon', 'fruit','4. 1')), ('celery', 'vegetable', '1.0 million pound', 20174plumber 01'), ('tomato', 'vegetable', '2.9' ), ('banana', 'fruit', '6.1'), ('walnut', 'nut', '28.5'), ('pistachio', 'nut', '38.11'), ('blueberry', 'fruit', '50.2') '2017 apprenticeship 05 Universe 15') Select * from products (name nvarchar (5) not null, place of sale nvarchar (3) not null, selling price money not null check (selling price = 0 and selling price ('Apple', 'Hebei', '5.0'), ('banana', 'Hunan', '6.2'), ('tomato', 'Beijing', '3.15'), ('cucumber', 'Hubei', '2.45'), ('celery') 'Hebei', '1.11'), ('strawberry', 'Beijing', '10.0'), ('Hami melon', 'Beijing', '8.98'), ('blueberry', 'Shanghai', '59.9'), ('walnut', 'Hainan', '35.8') Select * from sales