CREATE DATABASE Star; Solved

Description

1.CREATE DATABASE Star;

Executing the above statement in SQL Server will create two files: Star. (blank) and Star_log.(blank).The first file holds the data for the Star database and the second one keeps a log of any changes made to Star. (each is a file extension) “fill in the (blanks)”

2. Create Table ( blank )  (SID varchar(7) (blank) (SID LIKE ‘ (blank)  ‘)); Fill in the three blanks of the above statement such that it creates a table Student in schema CSE. This table contains only one column SID with a constraint which requires the second character of every SID value to be a digit ‘8’.

3. Will attack the table in the attach files section for this question

X and Y are two tables, where c1 and d1 are the primary key of each table, respectively. To complete the relationship in above diagram, the crow’s foot notation should be added to the end of the line that connects table (blank)

To create table Y, we should execute

CREATE TABLE Y (d1 int PRIMARY KEY, c1 int (blank) X(c1)); –one word

In the diagram of Question 3, if every row of Y must associate with one row of X, then we need to insert ______________ right after ‘c1 int’ of the CREATE TABLE Y statement. (two words)

In the diagram of Question 3, if we want to remove c2 column out of X, we must execute

ALTER TABLE X ____________ c2;  –two words

4.Complete the following statement to create a view called EmpOfBiggestDept, which returns all employees of the department(s) that has the most employees. (the 2nd blank contains two words)

CREATE VIEW EmpOfBiggestDept

AS SELECT * FROM Employee

   WHERE Dept_ID in (SELECT TOP 1 WITH TIES(blank)

From

(Blank)  Dept_ID

ORDER BY COUNT(*) DESC);

5. Suppose the MailBoxes table contains three columns, BoxNo, StuID, and MonthlyFee. Complete the UPDATE statement below to change MonthlyFee to $13 of students whose StuID begins with ‘G’. MonthlyFee of all others should be changed to $11. Hint: each blank is one word.

UPDATE MailBoxes

SET MonthlyFee = (blank)

(blank)  StuID like ‘G%’ (blank) 13

(blank) 11

(blank);

6. It is assumed that a student could have zero or one or more mailboxes but mailboxes cannot be shared. In the following statements, all columns of MailBoxes table can accept nulls except BoxNo.

SELECT StuID FROM Students

EXCEPT

SELECT StuID FROM MailBoxes;

Fill in the blank of each of the statements below such that each returns the same as the above one.

SELECT StuID

FROM Students 

WHERE StuID (blank)  (SELECT StuID FROM MailBoxes); –two words

SELECT s.StuID

FROM Students s (blank)  outer join MailBoxes m on s.StuID = m.StuID –one word

WHERE BoxNo is null;

SELECT StuID

FROM Students st

WHERE not (blank) (SELECT * FROM MailBoxes WHERE StuID = st.StuID); –one word

7. Suppose Region is a schema of a database called CRM and Kathy is a user of CRM. Complete the following statement so Kathy can insert data into applicable objects in Region.

GRANT (blank ) ON (blank)  TO Kathy;

8. use AP

go

create function TwoOrMoreDashInvoices()

returns @Inv table(InvID int, InvNum varchar(50), InvTotal money)

as

begin

  insert into @Inv

    select InvoiceID, InvoiceNumber, InvoiceTotal

    from Invoices

    where InvoiceNumber like ‘ (blank)  ‘;

(blank) ;

end

Fill in both blanks to complete the above statement to create a table-valued function called TwoOrMoreDashInvoices, which returns invoices whose InvoiceNumber contains two or more ‘-‘ (e.g., 111-92R-10097 or RTR-72-3662-X)

8b. Which of the following can be executed to test the function create in Question 14?

a. print TwoOrMoreDashInvoices()

b. exec TwoOrMoreDashInvoices()

c. select * from TwoOrMoreDashInvoices()

8c. The same function of Question 14 can be created by a different statement below, if the blank is filled in correctly.

use AP

go

create function TwoOrMoreDashInvoices()

returns (blank)

return

(select InvID=InvoiceID, InvNum=InvoiceNumber, InvTotal=InvoiceTotal

 from Invoices

 where ……….);