1 Create Tables and insert data Using either the SQL Server Management Studio, or Azure Data Studio, complete the following tasks, in order. You will be able to see how scripts work, inserting data,...

1 answer below »
s


1 Create Tables and insert data Using either the SQL Server Management Studio, or Azure Data Studio, complete the following tasks, in order. You will be able to see how scripts work, inserting data, see how views, procedures, and triggers work. Completing this lab will help in understanding a portion of project 2. Once the lab is completed, answer the questions on the last page of this lab. Please note: Please change all of the “USE” statements below to reflect the use of your initials instead of mine to: CIS111Asset Create Tables and insert data STEP1, Create the database CREATE DATABASE CIS111AssetRRJ GO STEP 2, create the tables USE CIS111AssetRRJ /* drop the tables */ IF OBJECT_ID('Employee') IS NOT NULL DROP TABLE Employee IF OBJECT_ID('Asset') IS NOT NULL DROP TABLE Asset IF OBJECT_ID('Agency') IS NOT NULL DROP TABLE Agency GO USE CIS111AssetRRJ /* create the agency table */ CREATE TABLE Agency (AgencyID INT NOT NULL IDENTITY PRIMARY KEY, AgencyName VARCHAR(30) NOT NULL, City VARCHAR(30) NOT NULL, State CHAR(2) NOT NULL) GO use CIS111AssetRRJ /* Create the employee table */ CREATE TABLE Employee (EmployeeID INT NOT NULL IDENTITY PRIMARY KEY, EmpFirstName VARCHAR(50) NOT NULL, EmpLastName VARCHAR(50) NOT NULL, DateOfBirth DATETIME NOT NULL, PhoneNumber VARCHAR(14), ReportTo INT NOT NULL, Pay MONEY NOT NULL, PayType CHAR(1) NOT NULL, CHECK (PayType IN ('Y', 'H', 'y', 'h')), AgencyID INT NOT NULL REFERENCES Agency(AgencyID)) GO USE CIS111AssetRRJ /* Create the Assets table */ CREATE TABLE Asset (AssetID INT NOT NULL IDENTITY PRIMARY KEY, PurchaseDate DATETIME NOT NULL, Description VARCHAR(50) NOT NULL, PurchaseAmount Money NOT NULL, SerialNo VARCHAR(14) NULL, AgencyID INT NOT NULL REFERENCES Agency(AgencyID)) --GO not necessary, as this is the last statement in the batch Create Tables and insert data STEP 3, Add the data Please open a new query window and insert the following statements. USE CIS111AssetRRJ /* add data to Agency table */ INSERT INTO Agency (AgencyName, City, State) VALUES ('One', 'Dearborn', 'MI'), ('Two', 'Southfield', 'MI'), ('Three', 'Royal Oak', 'MI') GO USE CIS111AssetRRJ /* add data to Employees table */ INSERT INTO Employee (EmpFirstName, EmpLastName, DateOfBirth, PhoneNumber, ReportTo, Pay, PayType, AgencyID) VALUES ('Bob', 'James', '1965/10/08', '3138456356', 1, 250000, 'y', 1 ), ('Fred', 'Sanford', '1960/12/10', '3138456357', 1, 75000, 'y', 1 ), ('Amy', 'Jones', '1988/08/30', '3138456358', 1, 75000, 'y', 2 ), ('Sam', 'Temm', '1960/11/22', '3138456359', 3, 75000, 'y', 2 ), ('Nicole', 'Simmons', '1972/01/01', '3138456301', 1, 10, 'h', 2 ), ('Andy', 'Donny', '1980/09/22', '3138456302', 2, 75000, 'h', 3 ), ('Debbie', 'Andrews', '1990/06/30', '3138456303', 3, 75000, 'y', 3 ), ('George', 'Frank', '1984/05/12', '3138456304', 2, 12, 'h', 3 ) GO USE CIS111AssetRRJ /* add data to Employees table */ INSERT INTO Asset (PurchaseDate, Description, PurchaseAmount, SerialNo, AgencyID) VALUES ('2010/03/03', 'Computer', 500, '94ew33098', 1 ), ('2011/03/03', 'Desk', 750, 'None', 1 ), ('2010/03/03', 'Microwave', 200, '22305', 2 ), ('2011/03/03', 'Painting', 3000, 'None', 2 ), ('2012/03/01', 'Painting', 1000, 'None', 2 ), ('2010/03/03', 'Desk', 2000, 'None', 3 ), ('2013/03/03', 'Microwave', 200, '8544rb', 3 ), ('2011/03/03', 'computer', 500, '1234ww30256', 3 ) Create Tables and insert data Please submit the following. Best to zip all files into one, then upload. 1. Submit all of your queries (step 2 and 3) 2. Create the queries to show the data has been added to the three tables. a. Don’t forget about the proper format of the queries, and the first 5 lines of the results/messages. Query Example Query Example This example will show you how to assemble a solution for printing. If you are submitting the query directly into Moodle, you simply need to upload your readable sql query directly. 1. Each solution must follow this prescribed format. Points will be deducted if not followed. 2. Each query and solution will be on a separate sheet. 3. Your name, the assignment number, and problem number, will be included in the query as a comment. You can use either a block or line comment. 4. Your name must be included in each query. 5. The messages output will be included for printed submissions. 6. The first 5 lines of the results tab will be included in printed solutions. 7. If you print all pages of the results output that produces more than one page to be printed, 1 point will be deducted for each additional page turned in. Query question: Display the employee first name, last name and full name in the following format: last name comma first name such as King, Robert. This list should ONLY include employees with the job title of Sales Representative that were born in the 1960’s (1960 - 1969). Use the Employees table. Query Solution: USE CIS11102_Northwind /* Assignment 1 Query example Solution Bob James You can use block comment, or line comment */ -- This is a line comment SELECT Firstname, LastName, LastName + ', ' + Firstname AS [Full Name] FROM Employees WHERE Title LIKE '%Sales Rep%' AND BirthDate BETWEEN '01-01-1960' AND '12/31/1969' Messages (4 row(s) affected) Results (don’t need to be lined up) Firstname LastName Full Name Janet Leverling Leverling, Janet Michael Suyama Suyama, Michael Robert King King, Robert Anne Dodsworth Dodsworth, Anne
chapter-11-lab-create-tables-and-insert-data-2bu5hhlp.pdf query-example-4-wo5zficu-sukggpwt-yy3e4vih.pdf
Answered 2 days AfterApr 16, 2022

Answer To: 1 Create Tables and insert data Using either the SQL Server Management Studio, or Azure Data Studio,...

Aditya answered on Apr 17 2022
94 Votes
Question :
Please submit the following. Best to zip all files into one, then upload. 1. Submit all of your queries (step 2 and 3) 2. Create the queries to show the data has been added to the three tables. a. Don’t forget about the proper format of the queries, and the first 5 lines of the results/messages.
Queries:
/*
Create Table and insert data
*/
-- Creating Database
CREATE DATABASE CIS111AssetT.T
GO
USE CIS111AssetT.T
/* drop the tables */
IF OBJECT_ID('Employee') IS NOT NULL
DROP TABLE Employee
IF OBJECT_ID('Asset') IS NOT NULL
DROP TABLE Asset
IF OBJECT_ID('Agency') IS NOT NULL
DROP TABLE Agency
GO
USE CIS111AssetT.T
/* create the agency table */
CREATE TABLE Agency
(AgencyID INT NOT NULL IDENTITY PRIMARY KEY,
AgencyName VARCHAR(30) NOT NULL,
City VARCHAR(30) NOT NULL,
State CHAR(2) NOT NULL)
GO
USE CIS111AssetT.T
/* Create the employee table */
CREATE TABLE Employee
(EmployeeID INT NOT NULL IDENTITY PRIMARY KEY,
EmpFirstName VARCHAR(50)...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?