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 »

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 XXXXXXXXXXNOT 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 XXXXXXXXXXINT 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', 'Dea
orn', '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', ' XXXXXXXXXX', 1, 250000, 'y', 1 ),
('Fred', 'Sanford', '1960/12/10', ' XXXXXXXXXX', 1, 75000, 'y', 1 ),
('Amy', 'Jones', '1988/08/30', ' XXXXXXXXXX', 1, 75000, 'y', 2 ),
('Sam', 'Temm', '1960/11/22', ' XXXXXXXXXX', 3, 75000, 'y', 2 ),
('Nicole', 'Simmons', '1972/01/01', ' XXXXXXXXXX', 1, 10, 'h', 2 ),
('Andy', 'Donny', '1980/09/22', ' XXXXXXXXXX', 2, 75000, 'h', 3 ),
('De
ie', 'Andrews', '1990/06/30', ' XXXXXXXXXX', 3, 75000, 'y', 3 ),
('George', 'Frank', '1984/05/12', ' XXXXXXXXXX', 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, '8544
', 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 XXXXXXXXXXUse 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 ' XXXXXXXXXX' 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
Answered 2 days AfterApr 16, 2022

Solution

Aditya answered on Apr 17 2022
10 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

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here