Assume you have an SQL Server Database designed with the following schema:

 

create table customer (id int not null identity,

 

name nvarchar(50) not null, primary key (id));

 

create table [order] (id int not null identity,

 

customerIdint not null,

 

total decimal not null default (0),

 

createDate DATETIME not null default(GETDATE()),

 

primary key (id));

 

create table lineItem (id int not null identity, orderIdint not null,

 

productIdint not null, itemPrice decimal not null, quantity int not null,

 

totalPriceint not null, primary key (id));

 

Write a query to return the number of customers that have placed 2 or more orders in the

 

month of January 2016. Feel free to make or define any necessary schema changes if non-

 

obvious.

 

Question 2:

 

From question 1: Execute the following statement

 

insert into customer ([id], [name]) VALUES (1, 'John Doe');

 

Unfortunately, you receive an error. Why did you get an error and what can you do to insert

 

this record with id value 1?

 

Question 3:

 

You have created this view

 

create view good_customers AS

 

SELECT [id], [name] FROM [dbo].[customer]

 

What can you do to prevent other developers from changing the name of the columns in the

 

customer table so the view does not break?

 

Question 4:

 

What are some advantages and disadvantages to using SQL Server to store your business

 

logic and back-end security (stored procedures and functions, etc)?

 

Question 5:

 

When is it OK to deviate from third-normal form and higher?

 

Question 6:

 

create table foo (id INT, name nvarchar(MAX));

 

INSERT INTO foo VALUES (1, 'John Doe');

 

INSERT INTO foo VALUES (2, 'John Smith');

 

INSERT INTO foo VALUES (3, 'Amy Smith');

 

INSERT INTO foo VALUES (3, 'Amy Adams');

 

INSERT INTO foo VALUES (4, 'John Adams');

 

INSERT INTO foo VALUES (5, 'John Quincy');

 

Suppose you have created and run the script above. You have accidentally set id value 3

 

twice. Write the appropriate SQL to correct the data.