sql implementation

The answers to these questions are best guesses of the contributor and may not be correct

Data Modeling & System Databases and Tables & Data Definition

101

Creation of view will result in a following table being updated …

sysobjects

syscomments

sysviews

syscolumns

Books-online: When you create a view, the name of the view is stored in the sysobjects table and the view's normalized query tree is stored in the sysprocedures table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the view creation statement is added to the syscomments table. This is similar to a stored procedure; but when a view is executed for the first time, only its query tree is stored in procedure cache. Each time a view is accessed, its execution plan is recompiled.

100	Creation of table will create an entry in …
?????	sysobjects of user db syscolumns

107	A null value is …
	UNKNOWN

91	How to ensure that pub_id which is not in publishers, is not inserted in titles.
?????	reference constraint triggers Foreign Key defaults

92	Two tables with common attributes , they are in …
?????	Relationship

96	The logical attribute of physical entity is called …
	column

108	The features of primary key are ?
????	A column or group of column is not null

109	Ensuring that primary key is unique and does not allow null value is called …
	Entity integrity

110

The various database objects are?

Table

Rules

Books-online: Database Object Owner

Database objects are tables, indexes, views, defaults, triggers, rules, and procedures.

14	Where are user-defined stored procedures stored?
	Master Msdb Current db Tempdb

15	In which database are system stored procedures stored?
	Master Msdb Current db Tempdb

16	Which database have the sysobjects table?
	All databases

17	In which database will the table be created with the create table ##tableName be stored?
	TempDB

34	A user create a temporary table #tmp1. If he wants the table to be accessed by everyone he should?
	Grand permission to all users Not possible at all Drop the table and create a ##temp1

36	Where will be temporary info is stored in server when we issue an SQL command using the DISTINCT clause?
	Is stored in database itself Is stored in tempDB Master database Model database

45	The optimizer hint can be specified in
	Select Where From Order by

A table has two indexes . The no. of pages utilized is ?

6 Kb

8 Kb

48 kb

256 Kb

1 extend (for the table) + 2 extend (for the 2 indexes) = 3 extends * 16Kb = 48Kb

77	The device info is stored in …
	User defined database Model Master Tempdb
78	*SELECT INTO #tl FROM cust. Where will #tl be stored?**
	Tempdb

50	System error messages are stored in?
	Master database

51	Every database …
	is a copy of model has user defined datatypes default size of 1 Mb user information

52	The default size of tempdb is …
	2 Mb

53	The SQL EXECUTIVE SERVICE schedule is supported by …
	Master Model Tempdb MSDB

54	Syshistory is used to …
	Find out the history of SQL server Find about the alerts and tasks executed .

66	Sales db uses a device DEV1 and the transaction log is using LOGDEV2. The Log device is full. Disk resize has already been given on the device LOGDEV2 and to increase the size of the transaction log, the command given is …
	ALTER DATABASE Sales on LOGDEV2 = 25 Outstanding solution

55	Devices are referred to in SQL statements using the …
	Physical name Logical name DEV_ID

56	When we require to create a device the command to use is…
	The master device is used. The device from default device pool is used

57	Removing master device from being as default is …
	Outstanding solution Adequate solution

58	While dropping a device I want to remove a physical file from the disk, the command sp_dropdevice …. DELFILE is an …
	Outstanding solution Adequate solution

59	Modifying database is done using …
	Alter database

60	To reduce the size of a database you need to be …
	SA or DBO Use the SHRINKDB command Resize if it less than model Give the final size

67	Sales db uses a device DEV1 and the transaction log is using LOGDEV2. The Log device is full. To increase the size of the transaction log , the command given is …
	DISK INIT and the ALTER DATABASE Will not work

73	How will you create your own error messages with a column name?
	Create your own stored procedure RAISEERROR should be used sp_addmessage

74	There is a table called cust_order existing. At the end of every month the sum of order qty will be added in to a table called cust_summary. So what is the relationship between cust_order and cust_summary?
	one-to-many one-to-one many-to-many many-to-one

76	How does Foreign key used to maintain referential integrity?
	It can be related to primary key of another table

76	How do you enforce integrity in a two tables that have a many to many relationship. An exhibit is given with the primary keys and foreign keys defined.
??	REFERENCES in the 2 tables REFERENCES in combination table CHECK in 2 tables CHECK in combination table.

114	There are 3 tables title, author and titleauthor . The rule posed on the 3 tables is that, every author should have written 0 - many books and every book should have at least one author. The titleauthor table connects titles and the author table. During the creation of the table how will you ensure these rules?
????	Create REFERENCES constraint on titleauthor(title_id) to the title table, and Create REFERENCES constraint on titleauthor(author_id) to the author table.

How do you increase the size of the transaction log?

With the ALTER DATABASE statement

Books-online: ALTER DATABASE statement

79	To increase the size of transaction log, the following statement is used…
	ALTER SALES ON LOG_DEV =25 Outstanding solution

133	You want to dump the transaction log, but the transaction has some free space which one will you choose?
?????	Dump Transaction with truncate_only. Dump Transaction with no_log .

21	How can you prevent transaction log from filling up?
	DUMP TRANSACTION to a dump device Use the DUMP TRANSACTION with the WITH NO_LOG or WITH TRUNCATE_ONLY Use the database option TRUNC ON CHKPT Use the DUMP TRANSACTION with NO_TRUNCATE

82	How do you prevent a log from filling up?
	Set checkpoint = TRUE Dump transaction with no_log Dump transaction with truncate_only

89	To increase the size of the device I give the following instructions
	DISK INIT Name = ..... Size = 9000 Will not work

90	Log is full; you have created a device called mydevice. DISKINIT has already been used . ALTER database on mydevice = 5 is given to increase the log size
	is an outstanding solution

Retrieving Data & Modifying Data

118

There is a publisher table and a title table. All the publishers who have published books whose average price is > $10 is to be listed.

SELECT a.pub_id, a.pub_name, AVG(b.price)

FROM publishers a , titles b

HAVING AVG(price) > 10

SELECT a.pub_id, a.pub_name, AVG(b.price)

FROM publishers a , titles b

WHERE a.pub_id = b.pub_id AND AVG(price) > 10

SELECT a.pub_id, a.pub_name, AVG(b.price)

FROM publishers a , titles b

GROUP BY a.pub_id, a.pub_name

HAVING AVG(price) > 10

You have two tables: titles and salesitems

CREATE TABLE titles ( titleID int CONSTRAINT pk_titles PRIMARY_KEY ... )

CREATE TABLE salesitems ( orderNo int NOT NULL, itemNo int NOT NULL, titleID int NOT NULL CONSTRAINT fk_salesitems REFERENCES(titles.titleID), qty int NOT NULL, ... )

You want to list sum of all titles by category which have sales.

SELECT category, title, SUM(qty) FROM titles t, salesitems s WHERE t.titleID =* s.titleID GROUP BY category, title

SELECT category, title, SUM(qty) FROM titles t, salesitems s WHERE t.titleID = s.titleID GROUP BY category, title HAVING SUM(qty) >= 0

SELECT category, title, SUM(qty) FROM titles t, salesitems s WHERE t.titleID = s.titleID OR qty=0 GROUP BY category, title

SELECT category, title, SUM(qty) FROM titles t, salesitems s WHERE t.titleID *= s.titleID GROUP BY category, title

26	To get a report of all the publishers names who’s published titles average price is greater then $10?
	SELECT name, AVG(price) FROM pubs WHERE AVG(price) >= $10 GROUP BY name Outstanding solution Adequate solution Will work but not a solution Will not work but appears to work Will not provide a solution and will not work

2	Which two of the following statements are a valid column alias change:
	SELECT ‘Customer Name’ = custname FROM … SELECT custname = ’Customer Name’ FROM SELECT custname ‘Customer Name’ FROM SELECT custname HEADING Customer Name FROM

11	You want to count how many number each different states have suppliers?
	SELECT DISTINCT count() FROM supplier SELECT DISTINCT state, count() FROM supplier** SELECT count(DISTINCT *) FROM supplier

The titles table has l_name and f_name columns. Write the SELECT statement to display the two in one columns.

SELECT l_name + f_name FROM titles

Books-online: SELECT statement

47	In order to find count of all unique cities from the author table, which SQL statement would you use?
	SELECT COUNT(DISTINCT city) FROM authors Please refer to question 11.
84	Display the name, price of the item, if price of item is null, then 0.00 has to be displayed, Choose two answers
	SELECT name, ISNULL(price, $0.00) SELECT name, IIF(price, $0.00) SELECT name, COALESCE(price, $0.00) SELECT name, IIF(price,NULL,'Unknown')

Display the count of unique titles

SELECT COUNT(DISTINCT title_id) FROM titles

Please refer to question 11.

Display the employee name and manager name from the table emp(emp_id, name, mgr_id)

SELECT a.name, b.name

FROM emp a, emp b

WHERE b.emp_id = a.mgr_id

Please refer to question 13.

49	The user wants to find out all those, which had some sales. The sql command given is:
	SELECT title_id FROM titles WHERE title_id = (SELECT title_id FROM sales) The above sql is: Outstanding Adequate Seems to be ok will not work. It is wrong and will not work.

You have an employee table and want to list all employees and their managers?

CREATE TABLE employee ( employeeID int NOT NULL, employeeName varchar(50) NOT NULL, ... managerID int NOT NULL )

SELECT 1.employeeName, 2.employeeName

FROM employee, employee

WHERE 1.employeeID = 2.employeeID

SELECT a.employeeName, b.employeeName

FROM employee a, employee b

WHERE a.employeeID = b.managerID

SELECT a.employeeName, b.employeeName

FROM employee a, b

WHERE b.managerID = a.employeeID

SELECT a.employeeName, b.employeeName

FROM employee a, employee b

WHERE b.managerID = a.employeeID

69	Which one is faster, TRUNCATE or DELETE?
	Truncate

75	What is HoldLock?
	Instructs the SQL server to hold the lock until completion of a transaction

14	To retrieve the database ID and the username:
	Use @@dbname, @@username DB_ID(), USER_NAME() Dbname(), userID() SELECT @ ID

120

How will you view the name of the current user and database?

SELECT @@USERNAME, @@DBNAME

SELECT USER_NAME(), DB_NAME()

SELECT USER_ID, DB_ID

Please refer to question 14.

121	How will you view all the books whose type is cooking?
	*SELECT FROM titles WHERE type = 'cooking'**

122	To list all the books starting with 'computers'
	SELECT * FROM titles WHERE name = %computer% Will meet the requirements and is an outstanding solution . Will meet the requirements and is an adequate solution . Will meet the requirements and is not a desirable solution . Will not meet the requirements and will not work

117

There are 2 tables cust_summary and cust_info. The cust_info table contains columns like cust_id, amount, name etc,. The group total of amount for each customer is calculated and updated in the total_amount of the cust_summary table. It should be seen that the cust_summary table is not updated when these updation are taking place.

BEGIN TRAN

SELECT cust_id, total = SUM(amount)

INTO #cust1

FROM cust_info

BROUP BY cust_id HOLD LOCK

UPDATE cust_summary

SET a.total_amount = b.total

FROM cust_summary a, #cust1 b

WHERE a.cust_id = b.cust_id

COMMIT TRAN

END TRAN

Will meet the requirements and is an outstanding solution

Will meet the requirements and is an adequate solution.

Will meet the requirements but is not a desirable solution.

Will not meet the requirements and will not work.

124

You want to wait for 10 minutes what will you do?

WAITFOR DELAY "00:10:00"

Books-online: WAITFOR statement

DELAY Instructs SQL Server to wait until the specified amount of time has passed, up to a maximum of 24 hours.

126

How to insert in values in the row by the user defined order?

INSERT employee (e_name, e_id) VALUES ("xxxxx", "x101" )

Books-online: INSERT statement

I have a table travel, custno is primary key and I have a column called t_id which I want to be unique what will I do?

t_id should be made unique

??????????????????????????

119

To ensure an Update-in-place?

The table should be marked for replication

The table should participate in a clustered index

The table should have fixed column width

The timestamp datatype should be used

number of discontiguous differing bytes is not more than 24.

For multirow updates:

The updated column must be fixed length.

The updated column cannot participate in a unique nonclustered index.

The updated column can participate in a nonunique nonclustered index only if the column is a fixed-width column (the index used to find rows cannot be the same as the updated column).

The table cannot include a column with the timestamp datatype.

116	Which 3 of the following will ensure for a fast BCP?
	Delete all triggers Set SELECT INTO/BULKCOPY ON to true Transactions not logged Drop all indexes

11	What can be done with Alter table?
	chane column datatype change column null option add column add constraint

37	How would you see all the records 30 days before today’s date?
	DATEADD( dd, -30, GETDATE() ) DATEDIFF( dd, 30, GETDATE() ) DATEADD( dd, GETDATE(),-30 ) Not possible

22	You have an address which has for some rows two columns, an for some rows three columns. All the columns are of the type varchar(50). What will you do in a CREATE TABLE statement?
	Create a column which allows NULL For different rows create different column lengths Create a column with default length of zero Create a column with a default value of zero

25	How to distinguish a table in a self join?
	Alias Defaults Columns Table

The answers to these questions are best guesses of the contributor and may not be correct

Where are user-defined stored procedures stored?

How do you enforce integrity in a two tables that have a many to many relationship. An exhibit is given with the primary keys and foreign keys defined.