View the step-by-step solution to:

RUNNING HEAD: ASSIGNMENT 3: SQL Query Design Before you begin, run the following two INSERT statements in TSQL2012: This is the same as the insert on...

Mil301,

As usual, here's the next assignment I require your help!

Thanks in Advance for the wonderful job as always!

RUNNING HEAD: ASSIGNMENT 3: SQL Query Design 1 Before you begin, run the following two INSERT statements in TSQL2012: -- This is the same as the insert on pg. 102 of the text.   -- Do not run this insert if you already have inserted this row into Suppliers. INSERT Production.Suppliers VALUES (N'Supplier XYZ', N'Jiru', N'Head of Security' , N'42 SekimaiMushashino-shi', N'Tokyo', NULL , N'01759', N'Japan', N'(02)0 4311-2609', NULL ); GO -- This inserts an unordered product into the Products table. INSERT Production.Products VALUES (N'Love Potion Number 9', 18, 2, 29.95, 0); GO For all join queries, use ANSI join syntax.
Background image of page 1
Running Head: Assignment 3: SQL Query Design 2 Part 1. Which aggregate function would you use to determine the latest order date value? a. Which of the following clauses is evaluated third : SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY? b. When creating a table, what are the two properties that must be specified for each column? What property should be specified for each column? c. What is the purpose of a database schema? d. How does the textbook define the "most efficient data type"? e. What is the purpose of a Primary Key constraint? f. State two differences between a Primary Key constraint and a Unique constraint. g. Why is it redundant to add a Unique constraint to a column defined as the Primary Key? h. What may a Foreign Key reference? i. In what namespace (database, schema, or table) must a constraint name be unique?
Background image of page 2
Show entire document
1 Abstract This document contains a sample Unit 5 Assignment. It also contains tutorials regarding how to perform the required tasks.  More information is in the  Query  Tutorial Items.zip  file, which can be found in Doc Sharing. Part 1.  Installation [Note: SQL Server Management Studio (SSMS) is open and connected to the SQL Server instance that was installed. The full connection string shows (green highlighting; your highlighting will probably be dark blue). The SSMS window is in the "restored" (not in the full screen) mode. The SSMS window is reduced in size so there is no extra white space and one can read the print. The ALT key was held down while pressing PrintScreen, to capture only the "active" window (SSMS), not the entire desktop. Please remove all blue notes and make other appropriate changes if you are using this document as your own template. ]
Background image of page 01
2
Background image of page 02
Show entire document
1 P ART 2. Q UERIES [Instructor note. The following was nice because it reminded the instructor of the database purpose and ERD. However, including this is not necessary.] The purpose of this database is to keep track of pets taken in by the animal rescue group that I belong to, Another Chance Animal Rescue. It includes data on pets being surrendered, where pets are being fostered, as well as information on adoptions. No changes were made to the design of the database. No records were added to any table. [Instructor note. Mentioning any changes is a good idea because it helps the instructor.] ERD: 1. Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and the output. The purpose of the query is to list all foster homes that accept cats. SELECT ContactFirstName, ContactLastName, Phone FROM FosterHome WHERE PetType = 'cats'; ContactFirstName ContactLastName Phone Pat Shawn 207-555-9876 Julie Prince 207-555-8888 Laura Martin 207-555-9999 Isabel Harper 207-555-4567 2. Get information from at least 3 tables in one statement and provide the output using the Join operator. State the purpose of the query; show the query and the output. The purpose of the query is to list all client families that have surrendered cats. SELECT ClientFamilyFirstName AS [First Name], ClientFamilyLastName AS [Last Name], SurrenderDate, Name AS [Name of Cat] FROM ClientFamily c JOIN Surrender s ON c.ClientFamilyID = s.ClientFamilyID JOIN Pet p ON p.PetID = s.PetID WHERE Species = 'Feline' ORDER BY SurrenderDate; 1
Background image of page 1
2 First Name Last Name SurrenderDate Name of Cat Olivia Shastov 2011-02-18 Snowflake Susan Ponti 2011-03-03 Smokey Kate Ender 2011-03-09 Yang Peter Goldberg 2011-04-22 Big Mike 2
Background image of page 2
Show entire document
TSQL2012 Database Diagram
Background image of page 1
-- create database vehicles USE master; GO CREATE DATABASE Vehicles; GO USE Vehicles; GO -- create table vehicles create table vehicles ( Year int null ,Make varchar(15) null ,Model varchar(15) null ,BodyStyle varchar(30) null ,Price int null ); go insert vehicles values (2000, 'Ford', 'Mustang', '2-Door Coupe', 8000); insert vehicles values (1999, 'Honda', 'Civic', '2-Door Coupe', 5000); insert vehicles values (2000, 'Chevrolet', 'Cavalier', '2-Door Coupe', 5000); insert vehicles values (1999, 'Ford', 'Taurus', '4-Door Sedan', 7000); insert vehicles values (1999, 'Toyota', 'Camry', '4-Door Sedan', 9000); insert vehicles values (2000, 'Toyota', 'Camry', '4-Door Sedan', 10000); insert vehicles values (2002, 'Toyota', 'Prius', 'Hybrid', 16000); insert vehicles values (2001, 'Toyota', 'Prius', 'Hybrid', 14000); insert vehicles values (2003, 'Toyota', 'Prius', 'Hybrid', 18000); insert vehicles values (2003, 'Honda', 'Civic', 'Hybrid', 17000); insert vehicles values (2003, 'Ford', 'Mustang', 'Convertible', 17000); insert vehicles values (1999, 'Ford', 'Mustang', 'Convertible', 9000); insert vehicles values (1998, 'Ford', 'Windstar', 'Cargo Van', 5000); insert vehicles values (2000, 'Ford', 'Windstar', 'Cargo Van', 9000);
Background image of page 1
insert vehicles values (2000, 'Chevrolet', 'Silverado', 'Pickup Truck', 15000); insert vehicles values (2001, 'Chevrolet', 'Silverado', 'Pickup Truck', 17000);
Background image of page 2
USE pubs; GO -- Sample CREATE TABLE code for table Owner and Dog /* The relationship: Owner - Dog ----------- One owner may own many dogs [M] One dog may have only one owner [1] Owner 1:M Dog "One" table is Owner; "many" table is Dog PK of Owner will be an FK in Dog */ -- Create the "One" table Owner, with the PK CREATE TABLE dbo.Owner ( OwnerID INT Primary Key NOT NULL ,LastName VARCHAR(60) NOT NULL ,FirstName VARCHAR(30) NOT NULL ,Phone CHAR(14) NULL ); GO -- Create the Owner table with PK having IDENTITY property DROP TABLE dbo.Owner; GO CREATE TABLE dbo.Owner ( OwnerID INT IDENTITY Primary Key NOT NULL ,LastName VARCHAR(60) NOT NULL ,FirstName VARCHAR(30) NOT NULL ,Phone CHAR(14) NULL ); GO -- Create the "Many" table Dog, -- with the FK defined with the column CREATE TABLE dbo.Dog ( DogID INT Primary Key NOT NULL ,Name VARCHAR(30) NOT NULL ,Birthday DATE NULL ,Alive CHAR(1) DEFAULT('Y') NOT NULL ,OwnerID INT CONSTRAINT FK_Dog_Owner FOREIGN KEY REFERENCES dbo.Owner (OwnerID) NOT NULL ); GO -- Create table Dog with the FK defined at the table level DROP TABLE dbo.Dog; GO CREATE TABLE dbo.Dog ( DogID INT Primary Key NOT NULL ,Name VARCHAR(30) NOT NULL ,Birthday DATE NULL
Background image of page 1
,Alive CHAR(1) DEFAULT('Y') NOT NULL ,OwnerID INT NOT NULL ,CONSTRAINT FK_Dog_Owner FOREIGN KEY (OwnerID) REFERENCES dbo.Owner (OwnerID) ); GO -- Create table Dog with the FK defined using ALTER TABLE DROP TABLE dbo.Dog; GO CREATE TABLE dbo.Dog ( DogID INT Primary Key NOT NULL ,Name VARCHAR(30) NOT NULL ,Birthday DATE NULL ,Alive CHAR(1) DEFAULT('Y') NOT NULL ,OwnerID INT NOT NULL ); GO ALTER TABLE dbo.Dog ADD CONSTRAINT FK_Dog_Owner FOREIGN KEY (OwnerID) REFERENCES dbo.Owner (OwnerID); GO -- Add a column to a table ALTER TABLE dbo.Owner ADD email VARCHAR(80) NULL; GO -- Add a CHECK constraint ALTER TABLE dbo.Owner --WITH CHECK ADD CONSTRAINT CK_Owner_email CHECK (email LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR email IS NULL); GO
Background image of page 2
Use Vehicles; GO SELECT * FROM Vehicles; GO --To determine the average of all the prices in the Price column SELECT AVG(Price) as AVGPrice FROM Vehicles; --The result would be an AVGPrice value of 11312. /* A WHERE clause could be used to limit the averaging to vehicles of year 2000 or later: */ SELECT AVG(Price) as AVGPrice FROM Vehicles WHERE Year >= 2000; -- The result would be a higher AVGPrice value of 13272. -- Suppose we want the average price for each body style. SELECT BodyStyle, AVG(Price) as AVGPrice FROM Vehicles GROUP BY BodyStyle; /* Notice that the above query obeys the rules for using GROUP BY: 1. There is at least one aggregate function AVG(Price) in the SELECT clause. 2. Every column in the SELECT list that is not an aggregate function must appear in the GROUP BY clause. This/these are the column(s) by which the data are "grouped." */ /* Now, suppose we cannot spend too much more than $10,000, so we want to list body styles that have an average price of under 10000. HAVING is used to choose a subset of rows from the grouping that satisfy the HAVING condition: */ SELECT BodyStyle, AVG(Price) as AVGPrice FROM Vehicles
Background image of page 1
GROUP BY BodyStyle HAVING AVG(Price) < 10000; /* Suppose we are interested in the average prices only for body styles of 4-Door Sedan or Hybrid. */ SELECT BodyStyle, AVG(Price) as AVGPrice FROM Vehicles WHERE BodyStyle = '4-Door Sedan' OR BodyStyle = 'Hybrid' GROUP BY BodyStyle; /* To summarize: 0. If you are asking a quesstion with "for each" in it, e.g. "What is the average price for each body style," then you will be writing a query that uses GROUP By. a. The "for each" item, e.g. body style, will be the non-aggregate. b. The aggregate item asked for, e.g. average price (use AVG) or "how many" (use COUNT) will be the aggregate. 1. In a query with one or more aggregates and one or more non-aggregate columns in the SELECT clause, you will use GROUP BY. 2. If you want to ±lter the result set based on a predicate containing a non-aggregate, you can ±lter before aggregation using the WHERE clause before the GROUP BY clause. 3. If you want to ±lter the result set based on the result of an aggregate, you must perform the aggregation and then ±lter; this is done by using the HAVING clause after the GROUP BY clause. */
Background image of page 2
Sign up to view the entire interaction

Top Answer

Dear , Kindly find the... View the full answer

Lab4solution.docx

Part 1:
a. Which aggregate function would you use to determine the latest order date value? ‘MAX function is used to determine the latest order date value. b. Which of the following clauses is...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online