SQLServer examination title and the answer 2

  First, multiple-choice questions (1-25 that no one at 26-30 per that a total of 2 hours 35 minutes) 
  1) the assumption that a user table, table included in the field: userid (int), username (varchar), password (varchar), and so on, need to set the table primary key, the following statement is correct ().    (Choose two) 
  a) If at the same time not have to repeat the username and password, then username and password can be grouped together as a primary key. 
  b) This table primary key design, according to choose the smallest primary key principles, the best use of userid as the primary key. 
  c) This table primary key design, according to choose the smallest primary key principles, the best use of username and password as a combination of keys. 
  d) If the userid as the primary key, then enter the userid out the values, to allow for air. 

  2) Microsoft SQL Server 2000 when installed on the user to create a sample database, including ().    (Choose two) 
  a) Pubs 
  b) Master 
  c) Northwind 
  d) Msdb 
  3) the following () statement TABLE_NAME extracted from the table before the 10 record.    (Choose one) 
  a) select * from TABLE_NAME where rowcount = 10 
  b) select TOP 10 * from TABLE_NAME 
  c) select TOP of 10 * from TABLE_NAME 
  d) select * from TABLE_NAME where rowcount <= 10 
  4) from the "Products" table for a price higher than the product name for the "one-off paper cups," the product of the records, this SQL statement for ().    (Choose one) 
  a) SELECT * FROM product WHERE price> 'disposable paper cups' 
  b) SELECT * FROM product WHERE price> (SELECT * FROM product WHERE product names> 'disposable paper cups' 
  c) SELECT * FROM product WHERE EXISTS product name = 'disposable paper cups' 
  d) SELECT * FROM product WHERE price> (SELECT FROM product WHERE price of the product name = 'disposable paper cups' 
  5) Find a student in the table all the phone numbers (listing: telephone) for the first 6 or 8, the third for the phone number 0 ().    (Choose one) 
  a) SELECT telephone FROM student WHERE telephone LIKE '[8,6]% 0 *' 
  b) SELECT telephone FROM student WHERE telephone LIKE '(8,6) * 0%' 
  c) SELECT telephone FROM student WHERE telephone LIKE '[8,6] _0%' 
  d) SELECT telephone FROM student WHERE telephone LIKE '[8,6] _0 *' 

  6) the existing table book, field: id (int), title (varchar), price (float); which id field is set, or use to insert language into book form data, the following sentence is wrong () .    (Choose one) 
  a) insert into book (id, title, price) values (1, 'java', 100) 
  b) insert into book (title, price) values ( 'java', 100) 
  c) insert into book values ( 'java', 100) 
  d) insert book values ( 'java', 100) 

  7) the existing table Employee, field: id (int), firstname (varchar), lastname (varchar); following sql statement is wrong ().    (Choose one) 
  a) select firstname +'.'+ lastname as' name 'from employee) 
  b) select firstname +'.'+ lastname = 'name' from employee 
  c) select 'name' = firstname +'.'+ lastname from employee 
  d) select firstname, lastname from employee 

  8) In sql server 2000, on the database is wrong is ().    (Choose one) 
  a) the default database on disk storage location is: SQL Server installation path \ Microsoft SQL Server \ MSSQL \ Data 
  b) a database should at least include a database files (*. mdf) and a transaction log files (*. ldf) 
  c) only when there is no data in the database of cases, the database can only be contracted operation. 
  d) A machine can copy from the database files and transaction log files to B machines, and then through the B machine implementation of the additional corresponding database operation, and database machines from A to B of the copy machine. 

  9) To delete all the data in book form, the following sentence is wrong ().    (Choose two) 
  a) truncate table book 
  b) delete * from book 
  c) drop table book 
  d) delete from book 

  10) Table grade student achievement in the field score (float), now all in the 55 to 60 minutes to raise the scores five minutes, the following sql statement is correct ().    (Choose two) 
  a) Update grade set score = score +5 
  b) Update grade set score = score +5 where score> = 55 or score <= 60 
  c) Update grade set score = score +5 where score between 55 and 60 
  d) Update grade set score = score +5 where score> = 55 and score <= 60 

  11) the existing RBL book, including field: price (float); now for a bibliography of the highest price for more information, the following statements is correct ().    (Choose two) 
  a) select top 1 * from book order by price asc 
  b) select top 1 * from book order by price desc 
  c) select top 1 * from book where price = (select max (price) from book) 
  d) select top 1 * from book where price = max (price) 

  12) the existing RBL book, including field: price price (float), category type (char); now for the average price of all categories, category names, the following statements is correct ().    (Choose one) 
  a) select avg (price), type from book group by type 
  b) select count (price), type from book group by price 
  c) select avg (price), type from book group by price 
  d) select count (price), type from book group by type 

  13) student in the table for all non-empty email message, the following statements is correct ().    (Choose one) 
  a) Select email from student where email! = null 
  b) Select email from student where email not is null 
  c) Select email from student where email <> null 
  d) Select email from student where email is not null 

  14) results in the field grade score on behalf of scores, the following () statement to return results in the lowest points.    (Choose two) 
  a) select max (score) from grade 
  b) select top 1 score from grade order by score asc 
  c) Select min (score) from grade 
  d) select top 1 score from grade order by score desc 

  15) existing orders table orders, containing personal information userid, product information productid, the following () statement to return to at least two have been ordered back to the productid? (Choose one) 
  a) select productid from orders where count (productid)> 1 
  b) select productid from orders where max (productid)> 1 
  c) select productid from orders where having count (productid)> 1 group by productid_ 
  d) select productid from orders group by productid having count (productid)> 1 

  16) on the aggregation function, the following is wrong is ().    (Choose one) 
  a) Sum to return to expressions of the total number of all, it can only be used to figure out the type. 
  b) Avg return to a few expressions of all, on average, can be used to type the number and date of the presentation. 
  c) Max Min can be used for character and the series. 
  d) Count can be used for the character out. 

  17) using the following () can not be fuzzy enquiries.    (Choose one) 
  a) OR 
  b) Not between 
  c) Not IN 
  d) Like 

  18) on the multi-table connection enquiries, the following () description is wrong.    (Choose one) 
  a) connection for the return of the result set rows may be larger than all conditions connected with the result set rows. 
  b) Multi-table connection enquiries must use keywords to JOIN 
  c) in connection for the return of result: all conditions connected with the data. 
  d) where clause in the conditions specified in the connection can be achieved in connection enquiries. 

  19) Sql statement: select * from students where SNO like'010 [^ 0]% [A, B, C]% ', may be out for the SNO is ().    (Choose two) 
  a) 01053090A # Hm3? 
  b) 01003090A01 
  c) 01053090D09 
  d) 0101A01 

  20) on Truncate table, the following () description is wrong.    (Choose two) 
  a) Truncate table with the Where clause may, in accordance with the conditions for removal. 
  b) Truncate table used to delete all the data in the table. 
  c) flip-flop on Truncate table invalid. 
  d) delete faster than Truncate table. 

  21) created a group called 'Customers' new table, while the new requirements in the table include table' clients' of all records, sql statement is ().    (Choose one) 
  a) Select * into customers from clients 
  b) Select into customers from clients 
  c) Insert into customers select * from clients 
  d) Insert customers select * from clients 

  22) on the primary key, the following () is wrong.    (Choose two) 
  a) primary key in the table can be used to ensure that there is no duplication of data lines. 
  b) a table must have a primary key. 
  c) a table there can be only one primary key. 
  d) rounded-out can only be set up on the main keys. 

  23) Assuming that the orders table orders used to store order information, cid on behalf of their clients code, money order for a single representative, is to query each customer's order number and each customer's total order amount, the following () sql statements can return to the correct Results.    (Choose one) 
  a) select cid, count (distinct (cid)), sum (money) from orders group by cid 
  b) select cid, count (distinct (cid)), sum (money) from orders order by cid 
  c) select cid, count (cid), sum (money) from orders order by cid 
  d) select cid, count (cid), sum (money) from orders group by cid 

  24) existing customers table customers (the main keys: Customer # cid), contains 10 lines of data, orders table orders (foreign key: Customer # cid), contains six data.    Executive sql statement: select * from customers right outer join orders on customers.cid = orders.cid.    Maximum return () of the record.    (Choose one) 
  a) 10 
  b) 6 
  c) 4 
  d) 0 

  25) The following description is not correct 
  a) storage process to achieve faster speed of execution. 
  b) Table embedded value of an equivalent function with the parameters of view. 
  c) do not specify the owner, called scalar function will go wrong. 
  d) can change any view of Table 

  26) Your database stores telephone numbers. Each telephone number is stored as an integer. You must format the telephone number to print on a report in the following format: 
  (999) 999-9999 
  You have selected the phone number into a local variable as follows: 
  DECLARE @ PhoneNumber int Which statement will correctly format the number? 
  a) SELECT 'Phone Number' = '(' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 3,0) + ')' + SUBSTRING (CONVERT (varchar (10), @ PhoneNurnber), 3,3 ) + '-' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 4,6) 
  b) SELECT 'Phone Number' = '(' + SUBSTRING (CONVERT (varchar (10), @ PhoneNuwber), 3,1) + ')' + SUBSTRING (CONVERT (varcher (10), @ PhoneNumber), 3,4 ) + '-' + SUBSTRING (CONVERT (varchar (10), @ PhoneMumber), 4,7) 
  c) SELECT 'Phone Number' = '(' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 0,3) + ')' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 3,3 ) + '-' + SUBSTRING (CONVERT (varchar (10), @ PhoneNurtiber), 6,4) 
  d) SELECT 'Phone Number' = '(' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 1,3) + ')' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 4,3 ) + '-' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 7,4) 

  27) You are a database developer for Wide World Importers. You are creating a database that will store order information. Orders will be entered in a client / server application. Each time a new order is entered, a unique order number must be assigned. Order numbers must be assigned in ascending order. An average of 10000 orders will be entered each day. You create a new table named Orders and add an OrderNumber column to this table. What should you do next? 
  a) Set the data type of the column to UniqueIdentifier. 
  b) Set the data type of the column to int, and set the IDENTITY property for the column. 
  c) Set the data type of the column to int. Create a user-defined function that selects the maximum order number in the table. 
  d) Set the data type of the column to int. Create a NextKey table, and add a NextOrder column to the table. Set the data type of the NextOrder column to int. Create a stored procedure to retrieve and update the value held in the NextKey. 

  28) You are creating a script that will execute this stored procedure. If the stored procedure executes successfully, it should report the year-to-date sales for the book title. If the stored procedure fails to execute, it should report the following message : 
  "No Sales Found" 
  How should you create the script? 
  a) DECLARE @ retval int 
  DECLARE @ ytd int 
  EXEC get_sales_for_title 'Net Etiquette', @ ytd 
  IF @ retval <0 
  PRINT 'No sales found' 
ELSE
  PRINT 'Year to date sales:' + STR (@ ytd) 
GO
  b) DECLARE @ retval int 
  DECLARE @ ytd int 
  EXEC get_sales_for_title 'Net Etiquette', @ ytd OUTPUT 
  IF @ retval <0 
  PRINT 'No sales found' 
ELSE
  PRINT 'Year to date sales:' + STR (@ ytd) 
GO
  c) DECLARE @ retval int 
  DECLARE @ ytd int 
  EXEC get_sales_for_title 'Net Etiquette', @ retval OUTPUT 
  IF @ retval <0 
  PRINT 'No sales found' 
ELSE
  PRINT 'Year to date sales:' + STR (@ ytd) 
GO
  d) DECLARE @ retval int 
  DECLARE @ ytd int 
  EXEC @ retval = get_sales_for_title 'Net Etiquette', @ ytd 
OUTPUT
  IF @ retval <0 
  PRINT 'No sales found' 
ELSE
  PRINT 'Year to date sales:' + STR (@ ytd) 
GO

  29) You are a database developer for an insurance company. Information about the company's insurance policies is stored in a SQL Server 2000 database. You create a table named policy for this database by using the script shown below: 
  CREATE TABLE Policy 
(
  PolicyNumber int NOT NULL DEFAULT (0), 
  InsuredLastName CHAR (30) NOT NULL, 
  InsuredFirstName CHAR (20) NOT NULL, 
  InsuredBirthDate dattime NOT NULL, 
  PolicyDate datetime NOT NULL, 
  FaceAmount money NOT NULL, 
  CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber) 
)
  Each time the company sells a new policy, the policy must be assigned a unique policy number. The database must assign a new policy number when a new policy is entered. What should you do? 
  a) Create an INSTEAD OF INSERT trigger to generate a new policy number, and include the policy number in the data instead into the table. 
  b) Create an INSTEAD OF UPDATE trigger to generate a new policy number, and include the policy number in the data inserted into the table. 
  c) Create an AFTER UPDATE trigger to generate a new policy number, and include the policy number in the data inserted into the table. 
  d) Replace the DEFAULT constraint with a AFTER INSERT trigger that generates a new policy number and includes the policy number in the data inserted into the table. 

  30) You are a database developer for a marketing firm. You have designed a quarterly sales view. This view joins several tables and calculates aggregate information. You create a view. You want to provide a parameterised query to access the data contained in your view . The output will be used in other SELECT lists. How should you accomplish this goal? 
  a) Use an ALTER VIEW statement to add the parameter value to the view definition. 
  b) Create a stored procedure that accepts the parameter as input and returns a rowset with the result set. 
  c) Create a scalar user-defined function that accepts the parameter as input. 
  d) Create an inline user-defined function that accepts the parameter as input. 

  2, Tian Kongti (each a space of 25 minutes) 
  1, and R is 1 NF, if and only if all the basis of domain includes only ________。 
  2, the main keyword should be ________ of ,__________ of ,__________ of. 
  3, and R is 3 NF, if and only if R is 2 NF, and all non-PK attributes are __________ in the PK. 
  4, Transact-SQL language contains language ,_________ _________ language ,_________ language. 
  5, elimination of duplication out the use of keywords is _________, _________ number of enquiries from the operator to create a single result set, the Panel submitted a statement to ________. 
  6, SQLServer the two licensing models were ___________ and _____________。 
  7, a data table and his party have their 5 KB, with 10,000 lines of data, then we need at least ______ M database space. 
  8, data integrity into __________,__________,__________. 
  9, each allowed to have a cluster _______ index SQLServer ,___________ index is the default choice. 
  10, ________ can use keywords to show non-encrypted information storage process, the implementation of the storage process is _________, keyword statistics are average for the aggregation function ________, revise the target keywords for _______, delete the key targets The word for _________。 
  11 ,________ database of all the temporary table and temporary storage process. 

  3, Jane Answer (15 points) 
  1, please briefly outline how the second paradigm (3) 
  2, please briefly UPDATE trigger principle (3) 
  3, mandatory use integrity, briefly SQL Server users to prohibit the operation (3 points) 
  4, outlining the steps for the relevant sub-(3) 
  5, outlining the use of the index and the index for non-use (3) 
  On that machine (25 points) 
  1, (12) Description of the problem: 
  Known models: 
  S (SNO, SNAME) student relations.    SNO for the study of, SNAME to name 
  C (CNO, CNAME, CTEACHER) courses relations.    CNO, for the courses, CNAME the course of, CTEACHER Renke for teachers 
  SC (SNO, CNO, SCGRADE) between classes.    SCGRADE for results 
  1. Elective not identify the "Li Ming" teachers teach the curriculum for all students names (4 points) 

  2. There are two listed above (including 2) failed course, a student names and the average results (4 points) 

  3. Lists of both the "1" of courses and learn "2" of course the names of all students (4 points) 
  Second, the definition of a metric conversion of the 2-band scalar function.    (4 points) 
  Third, demand deposits, "depositors" through "single-access" and "earned on savings" in contact.    The assumption that depositors include: account number, name, phone number, address, deposits, "earned on savings," including: earned on savings number, name, phone, address (assuming a depositor can be earned on savings in a different access) 
  1, designed more to write the phrase forms (4) 
  2, to create a complete flip-flop TR1 below: 
  When the "deposit" of inserting data in the table, if access should be a symbol = table changes to the depositors with deposits in the amount of access, if access to mark = 0 table so that depositors should change minus the amount of deposits From the amount, if the balance is less than the balance the wrong show.    (5) 

  The answer: 

  On one, Tian Kongti 
  1) a, b 2) a, c 3) b 4) d 5) c 6) a 7) b 8) c 9) b, c 10) c, d 11) b, c 12) a 13) d 14 ) c, d 15) d 16) b 17) d 18) b 19) a, d 20) a, d 21) a 22) a, c 23) d 24) b 25) d 26) d 27) b 28 ) d 29) a 30) d 

  2, Tian Kongti 
  1, the value of atomic 
  2, stability, the most simple, familiar and 
  3, rely on non-delivery 
  4, data definition, data control, data manipulation 
  5, distinct, UNION, COMMIT TRANSACTION 
  6, NT only verify mode, mixed-mode 
  7,80 
  8, the domain integrity, physical integrity, the integrity of information 
  9, a non-cluster 
  10, sp_help, EXEC, AVG, ALTER, DROP 
  11, tempdb 

  Next, 
  Data prepared 

  create table s (sno int, sName varchar (100)) 

  create table c (cno int, cName varchar (100), CTEACHER varchar (100)) 

  create table sc (sno int, cno int, scGrade int) 

  insert into s 
  values (1, 'lfm1') 
  insert into s 
  values (2, 'lfm2') 
  insert into s 
  values (3, 'lfm3') 
  insert into s 
  values (4, 'lfm4') 
  insert into s 
  values (5, 'lfm5') 
  insert into s 
  values (6, 'lfm6') 

  insert into c 
  values (1, 'ch1', 'Li') 
  insert into c 
  values (2, 'ch2', 'Wang Gang') 
  insert into c 
  values (3, 'ch3', 'll') 
  insert into c 
  values (4, 'ch4', 'ff') 
  insert into c 
  values (5, 'ch5', 'ffd') 
  insert into c 
  values (6, 'ch6', 'se') 
  insert into c 
  values (7, 'ch7', 's') 

  insert into sc 
  values (1,2,80) 
  insert into sc 
  values (2,2,50) 
  insert into sc 
  values (1,1,60) 
  insert into sc 
  values (1,3,90) 
  insert into sc 
  values (3,2,55) 
  insert into sc 
  values (1,6,77) 
  insert into sc 
  values (3,4,80) 
  insert into sc 
  values (4,2,70) 

  1. Elective not identify the "Li Ming" teachers teach the curriculum for all students Name 
  — Implementation code: 
  SELECT SNAME FROM S 
  WHERE NOT EXISTS ( 
  SELECT * FROM SC, C 
  WHERE SC.CNO = C. CNO 
  AND CNAME = 'Li' 
  AND SC.SNO = S. SNO) 

  select sname from s 
  where sno not in 
  (select s.sno from s 
  inner join sc 
  on sc.sno = s.sno 
  inner join c 
  on c.cno = sc.cno 
  where cteacher = 'Li') 

  2. There are two listed above (including 2) failed course, a student names and the average results 
  — Implementation code: 
  SELECT S. SNO, S. SNAME, AVG_SCGRADE = AVG (SC.SCGRADE) 
  FROM S, SC, ( 
  SELECT SNO 
  FROM SC 
  WHERE SCGRADE <60 
  GROUP BY SNO 
  HAVING COUNT (DISTINCT CNO)> = 2 
  ) A WHERE S. SNO = A. SNO AND SC.SNO = A. SNO 
  GROUP BY S. SNO, S. SNAME 

  select s.sno, avg (scGrade) from s, sc 
  where s.sno in ( 
  select sc.sno from sc 
  inner join c 
  on c.cno = sc.cno 
  where sc.scGrade <60 
  group by sno 
  having count (*)>= 2) 
  and s.sno = sc.sno 
  group by s.sno 

  3. Lists of both the "1" of courses and learn "2" of course the names of all students 
  — Implementation code: 
  SELECT S. SNO, S. SNAME 
  FROM S, ( 
  SELECT SC.SNO 
  FROM SC, C 
  WHERE SC.CNO = C. CNO 
  AND C. CNO IN ('1 ','2') 
  GROUP BY SNO 
  HAVING COUNT (DISTINCT c.CNO) = 2 
  ) SC WHERE S. SNO = SC.SNO 

  select S. SNO, S. SNAME from sc, s 
  where cno = 1 and sc.sno in (select sno from sc where cno = 2) and s.sno = sc.sno 

  Two, 
  create function convert2 (@ num int) 
  returns varchar (100) 
as
begin
  declare @ re varchar (100) 
  set @ re ='' 
  while @ num> 0 
  select @ re = substring ('01 ', @ num% 2 +1,1) + @ re 
  , @ num = @ num / 2 
  return @ re 
end

  select dbo.convert2 (8) 
  Three, 
  1, 
  CREATE TABLE depositors 
(
  Account INT PRIMARY KEY, name CHAR (10), phone INT, address CHAR (10), deposits for the MONEY 
)
GO

  CREATE TABLE earned on savings 
(
  No. earned on savings INT PRIMARY KEY, name CHAR (10), phone INT, address CHAR (10) 
)
GO

  CREATE TABLE access to single - 
(
  Account INT NOT NULL, earned on savings # INT NOT NULL, access date DATETIME NOT NULL, 
  Access signs INT NOT NULL, access to the amount of MONEY 
)
GO
  2. 
  CREATE TRIGGER TR1 
  ON access to single - 
  FOR INSERT 
AS
  DECLARE @ BZ INT, @ MONEY MONEY, @ ZH INT 
  SELECT @ BZ = access signs, @ MONEY = amount of access, @ ZH = account 
  FROM INSERTED 
  IF @ BZ = 0 
BEGIN
  UPDATE depositors 
  SET deposits = deposits – @ MONEY 
  WHERE account = @ ZH 
  declare @ sy money 
  select @ sy = deposits from savers 
  WHERE account = @ ZH 
  if @ sy <0 
begin
  raiserror ( 'insufficient funds', 16,1) 
rollback
end
END
  IF @ BZ = 1 
BEGIN
  UPDATE depositors 
  SET deposits = deposits + @ MONEY 
  WHERE account = @ ZH 
END

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Tags: