Highlights of tsql test questions: Examples of students' course selection system requirements are provided with my personal answers.

/* Table 1. employee table ): eid name department job email password 10001 Li Ming SBB eg 10003 Li Yiping Luke ITM 11045 Li Jie SBB eg 10044 Hu Fei MTD etn 10009 Xu zhonggang SBB eg 10023 Li Yan SBB etn 20460 Lu mingsheng MTD etn 20078 Zhang Qing mmm eg 20001 Li Luke etn

Table 2. training (training table) courseid Eid course grade Order 1 10001 T-SQL 60 3 11045 Oracle 71 2 20460 Java 34 1 10003 T-SQL 59 3 10001 Oracle 90 2 20001 Java 12 2 20078 Java 76 2 10003 Java 78 3 30001 Oracle 71 3 20048 Oracle 36

The following uses a T-SQL statement based on SQL Server 2000 to complete 1. Create a database training. 2. Create the structure of the table "employee" and "training", table 1 and table 2, and set the primary key. 3. Use an SQL statement to insert the data in the preceding two tables into the created table, and use an SQL statement to complete the operation. 4. count the number of people in each department and the number of people surnamed "Li" in each department, and use an SQL statement. 5. list all employees who participate in the training. The Eid, name, department, and course must be displayed in one SQL statement. 6. filter out the list of persons not attending the training. The list is displayed in table 1 and completed with an SQL statement. 7. Update the employee's email. The rule is: add the employee's department name and employee name plus "@ dhcc.com.cn" and use an SQL statement. 8. list all employees with the highest scores in each course. The Eid, name, department, course, and grade must be displayed and completed with an SQL statement. 9. Insert all employee numbers in Table 2 but not in Table 1 into table 1 and use an SQL statement. 10. analyze the relationship between table 1 and table 2, establish the reference relationship between table 1 and table 2, and implement cascade operations. 11. Use a trigger to perform operations related to question 10th. 12. Count and print the number of students of each course score segment: course Id, course name, [100-85], [84-70], [69-60], [<60] 13. Calculate and print the average score and the percentage of the average score (in N rows) of each subject in the ascending order of the percentage of each subject's failure rate and average score (in N rows): (that is to analyze which course is difficult) course Id, course name, average score, failed percentage 14. Rank each course based on the score, and update it to the order column of table 2 (in case of parallel operation allowed), and implement it using procedure (two methods are required). use an SQL statement. B. Complete with a cursor. */-- Solution 14 -- B is completed with a cursor. declare @ grade int, @ course varchar (6), @ ID char (5) Declare training_cursor cursor -- defines the cursor for select grade, course, ID from training -- assign value to open training_cursor -- open the cursor and load it To the memory. fetch next from training_cursor into @ grade, @ course, @ ID while @ fetch_status = 0 begin Update training set order0 = (select count (distinct grade) from training where grade >=@ grade and course = @ course) from training a where id = @ ID fetch next from training_cursor into @ grade, @ course, @ ID end close training_cursor -- close the cursor deallocate training_cursor -- release the memory occupied by the cursor.

-- A statement is completed. update training set order0 = (select count (distinct grade) from training where grade> =. grade and course =. course) from training a -- the query status is as follows. select *, order00 = (select count (distinct grade) from training where grade> =. grade and course =. course) from training a order by courseid, order00 -- sql2005 implementation method. select *, dense_rank () over (partition by courseid order by grade DESC) as [dense_rank] From training order by courseid -- Solution 13 select courseid [course Id], max (course) [Course name], AVG (grade) [average score], cast (sum (case when Grade <60 then 1 end) as float)/count (courseid) * 100 as varchar) + '%' [failed percentage] From training group by courseid order by [failed percentage] ASC, average score DESC

-- Solution 12 select courseid, course, (case when grade between 85 and 100 then grade end) [100-85], (case when grade between 84 and 70 then grade end) [84-70], (case when grade between 60 and 69 then grade end) [69-60], (case when Grade <60 then grade end) [<60] From training order by courseid

-- Solution 11 alter table [DBO]. [Training] Drop constraint fk_training_employee -- to reflect the use of triggers, delete the foreign key constraint that answers 10 first.

Alter trigger training_insert on training instead of insert as begin set nocount on declare @ Eid char (5), @ showmessage varchar (100) set @ eid = (select Eid from inserted) set @ showmessage = @ Eid + 'data is not exists' if not exists (select 1 from employee where eid = @ EID) raiserror (@ showmessage, -- message text. 10, -- severity, 1, -- state, N 'number', -- first argument. @ EID); else insert into training (courseid, Eid, course, grade) Select courseid, Eid, course, grade from inserted end -- test with test statement. insert into training (courseid, Eid, course, grade) values ('1', '000000','t-SQL ', 80)

Select * from training -- or ten records. No insert is successful.

-- Solution 10 alter table employee alter column Eid char (5) not null go alter table employee add constraint [pk_employee_employee] primary key clustered ([Eid] ASC) go alter table training add constraint fk_training_eid foreign key (EID) References employee (EID) Go

-- Solution 9 insert into employee (EID) Select Eid from training a where not exists (select 1 from employee where eid = A. Eid)

-- Solution 8 select max (. EID) Eid, max (. name) [name], max (. department) department, B. course, max (B. grade) grade from employee a join training B on. eid = B. eid group by B. course order by grade

-- Solution 7 update employee set email = Department + name + '@ dhcc.com.cn' -- Solution 6 select * from employee a where not exists (select 1 from training where. eid = EID) Select * from employee where EID not in (select distinct Eid from training)

-- Solution 5 select a. Eid, A. Name, A. Department, B. Course from employee a join training B on A. eid = B. Eid order by A. Eid

-- Solution 4 select * from (select count (name) Total, department from employee group by Department) A left join (select count (name) surnamed Li, department from employee where name like 'Lee % 'group by Department) B on. department = B. department

-- Solution 3 insert into employee (Eid, name, department, job) Select '000000', 'lilim', 'sbb', 'eg 'Union all select '000000 ', 'Li ping', 'Luke ', 'itm' Union all select '000000', 'Li jie', 'sbb', 'eg' Union all select '000000 ', 'hof', 'mtd', 'etn' Union all select '000000', 'xu zhonggang ', 'sbb', 'eg' Union all select '000000 ', 'Li yany', 'sbb', 'etn' Union all select '000000', 'lu mingsheng ', 'mtd', 'etn' Union all select '000000 ', 'zhang qing', 'mmm', 'eg 'Union all select '000000', 'lily', 'luke', 'etn' go insert into training (courseid, Eid, course, grade) select 1, '123','t-SQL ', 60 Union all select 3, '123', 'oracle', 71 Union all select 2, '123 ', 'java', 34 Union all select 1, '000000','t-SQL ', 59 Union all select 3, '123', 'oracle', 90 Union all select 2, '123', 'java', 12 Union all select 2, '123', 'java', 76 Union all select 2, '123', 'java ', 78 Union all select 3, '123', 'oracle ', 71 Union all select 3, '123', 'oracle', 36

-- Solution 2 Use tranining go create table employee (ID int identity (1, 1), Eid char (5) not null, name nvarchar (4), department varchar (8 ), job varchar (3), email varchar (50), password varchar (100) Go create table training (ID int identity (), courseid int, Eid char (5 ), course varchar (6), grade int, order0 varchar (50 ))

-- Solution 1 create database tranining on (name = n' Traning _ data', filename = n' D: Database raning_dat.mdf ', size = 3072kb, filegrowth = 1024kb) log On (name = n' Traning _ log', filename = n' D: Database raning_log.ldf', size = 1024kb, filegrowth = 10%)

1. Test Question: Experiment 1. according to the Program Write result: declare @ A bigint set @ A = 32768 select @ A, datalength (@ A) Go requirements: (1) write the program running result. A: As a result, the program types are changed to three types: smallint, tinyint, and bigint, respectively, and the program running result is obtained. A: smallint and tinyint will overflow in arithmetic operations. bigint will display the values of 32768,8. 2. Declare a local variable of the real type and assign the values 321.12 and 87654321.456 to it respectively to display the result. A: declare @ s real set @ s = 321.12 select @ s set @ s = 87654321.456 -- The SQL-92 synonym for real is float (24 ). Float (1 ~ 24) the precision is 7 digits. If the data exceeds the displayed value, it is a scientific notation. Select @ s go

3. according to the program writing result: declare @ C nvarchar (10) set @ C = '文 'select @ C, datalength (@ C) Go requirements: (1) write the program running result. Answer: Display: '', 1. Because if n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified when cast and convert functions are used, the default length is 30. (2) what are the results of changing the type to Char (2), char (3), char (5), and char (10? A: Because it is defined as a char fixed length character, and a Chinese character occupies two bytes, it is displayed as follows. in addition, the data type length is displayed as the specified defined length. the displayed values correspond to, respectively, letter 2, letter 3, information 5, Information College, 10

(3) If you change the type to varchar, varchar (2), varchar (3), and varchar (10), what is the result? A: Change the variable length to only save the actual characters that can be put down. The numbers are 0 to 2 to 2 to 8.

(4) If you change the type to nchar (2), nchar (3), nchar (5), and nchar (10), what is the result? Answer: Because of nchar (n), the storage size is twice n Bytes. The default value is 1. Information 4: informatics 6: Information Institute 10: Information Institute 20

(5) If you change the type to nvarchar, nvarchar (2), and nvarchar (10), what are the results? Display: letter 2 Information 4 Information Institute 8 (6) compares the differences among char, varchar, nchar, and nvarchar data types. A: Char is a fixed-length character. It can be used if the data items in the column are of the same size. varchar is a variable-length character. It is used if the data items in a column are significantly different. nchar is a general fixed-length character used to store universal data, such as Chinese and English. if the column data item size is similar, you can use the nvarchar Universal variable-length character to store Chinese and English characters. If the column data item size difference is large, you can use it.

4. Declare a variable of the date and time type. Requirement 1: Assign the value of today's date to the variable and display the result. Requirement 2: assign a value to the variable in the format of month, day, and year and display the result. A: declare @ DT datetime set @ dt = getdate () Select @ DT select month (@ DT) [month], Day (@ DT) [Day], year (@ DT) [year] Go

5. Write the result: select lower ('abc') + space (5) + rtrim (ltrim ('Hello! ') A: Hello ABC! (Five consecutive spaces)

6. Write the function expression and Result: Calculate the length of the string 'SQL Server database management system'. A: Select Len (' SQL Server database management system ')

7. Write a function expression: Calculate the current system date and time of the server. Answer: Select getdate ()

8. Mary's birthday is 1987. Use the date function to calculate Mary's current age. Answer: Select datediff (year, '2017-12-23 ', getdate ())

9. evaluate: Y = {x + 10x<0 x = 0 X-10 x> 10 10. if the student table contains more boys, the student table displays more boys and more boys. Select case when (select count (*) [num] from student where sex = 0)> (select count (*) [num] from student)/2 then 'boys' multiple 'end'

11. view the average score (course number: 'c801'). If the score is equal to or greater than 60, the 'data structure average pass 'and the average score are displayed. Otherwise, the 'data structure average score is fail '. select case when AVG (score)> 60 then 'data structure average pass 'else' Data Structure average pass 'end, AVG (score) from course group by course No. Having course No. = 'c801'

Experiment 2 1. Use a stored procedure without parameters (1) create a stored procedure my_proc, query the student ID, name, gender, age, and Department of all computer girls in the "Student table. (2) execute the Stored Procedure (3) modify the stored procedure so that it can query all the basic information of girls in Computer Science. Answer: (1) Create proc my_proc as begin select student ID, name, gender, age, department from student table where Department = 'computer Department 'end (2) exec my_proc (3) alter proc my_proc as begin select * from student table where Department = 'computer system' end

2. Stored Procedure with input parameters (1) create a stored procedure my_procsex, enable the student to query the student ID, name, gender, age, and Department of the male or female student in the "Student table". (2) perform the stored procedure. Answer: (1) create proc my_procsex (@ student ID varchar (64) = '', @ name varchar (64) ='', @ gender char (2) = '', @ age Int = '', @ Department varchar (64) ='') as begin select * from student table where (student ID = @ student ID or @ student ID = '') and (name = @ name or @ name = '')... end (2) exec my_procsex

3. Stored Procedures with input/output parameters (1) create a stored procedure my_procage so that it can query the student age based on the student name. (When the student does not exist, give a prompt) (2) execute the Stored Procedure answer (1) alter proc my_procage (@ name varchar (16), @ MSG nvarchar (32) Output) as begin if (select count (*) from student table where name = @ name or @ name is null) <1 begin set @ MSG = 'this person does not exist! 'End select borndate from student table where name = @ name or @ name is null end (2) Declare @ MSG nvarchar (32) exec my_procage '2 2', @ MSG output select @ msg