Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Exam Questions with Solution - Database Administration | ITEC 3500, Exams of Deductive Database Systems

Material Type: Exam; Class: DATABASE ADMINISTRATION; Subject: Information Technology; University: Armstrong Atlantic State University; Term: Spring 1992;

Typology: Exams

Pre 2010

Uploaded on 08/04/2009

koofers-user-dws
koofers-user-dws 🇺🇸

10 documents

1 / 5

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
ITEC 3500, Extra credit 5% of the total grade, Due: April 30th before class.
Show the SQL commands used to achieve the following tasks. Use SQL Server as your test DBMS.
1. Create a database called ExtraCredit and a table called Employees using the following info: (15 pts)
Table Columns
Data
Type
Length
of Data Allow Null?
Employees EmployeeID int No
Employees LastName nvarchar 20 No
Employees FirstName nvarchar 10 No
Employees Title nvarchar 30 YES
Employees TitleOfCourtesy nvarchar 25 YES
Employees BirthDate datetime YES
Employees HireDate datetime YES
Employees Address nvarchar 60 YES
Employees City nvarchar 15 YES
Employees Region nvarchar 15 YES
Employees PostalCode nvarchar 10 YES
Employees Country nvarchar 15 YES
Employees HomePhone nvarchar 24 YES
Employees Extension nvarchar 4 YES
Employees ReportsTo int YES
Set the EmployeeID as the primary key.
2. Then use the following info to insert data into the table, set a constraint that the hire date must be later than the birth date. (15
points)
EmployeeID LastName FirstName Title
TitleOfCourte
sy
BirthDat
e HireDate Address City
Regi
on
Postal
Code
Countr
y
HomePh
one
Extensio
n
Reports
To
pf3
pf4
pf5

Partial preview of the text

Download Exam Questions with Solution - Database Administration | ITEC 3500 and more Exams Deductive Database Systems in PDF only on Docsity!

ITEC 3500, Extra credit 5% of the total grade, Due: April 30th^ before class.

Show the SQL commands used to achieve the following tasks. Use SQL Server as your test DBMS.

1. Create a database called ExtraCredit and a table called Employees using the following info: (15 pts)

Table Columns

Data

Type

Length

of Data Allow Null?

Employees EmployeeID int No

Employees LastName nvarchar 20 No

Employees FirstName nvarchar 10 No

Employees Title nvarchar 30 YES

Employees TitleOfCourtesy nvarchar 25 YES

Employees BirthDate datetime YES

Employees HireDate datetime YES

Employees Address nvarchar 60 YES

Employees City nvarchar 15 YES

Employees Region nvarchar 15 YES

Employees PostalCode nvarchar 10 YES

Employees Country nvarchar 15 YES

Employees HomePhone nvarchar 24 YES

Employees Extension nvarchar 4 YES

Employees ReportsTo int YES

Set the EmployeeID as the primary key.

2. Then use the following info to insert data into the table, set a constraint that the hire date must be later than the birth date. (

points)

EmployeeID LastName FirstName Title TitleOfCourte sy BirthDat e HireDate Address City Regi on Postal Code Countr y HomePh one Extensio n Reports To

1 Davolio Nancy Sales Representative Ms.

507 - 20th Ave. E. Apt. 2A Seattle WA 98122 USA

3 Leverling Janet Sales Representative Ms.

722 Moss Bay Blvd. Kirkland WA 98033 USA

4 Peacock Margaret Sales Representative Mrs.

4110 Old Redmond Rd. Redmon d WA 98052 USA

6 Suyama Michael Sales Representative Mr. 7/2/

Coventry House Miner Rd. London

NUL

L

EC

7JR

UK

7 King Robert Sales Representative Mr.

Edgeham Hollow Winchester Way London

NUL

L

RG

9SP UK

8 Callahan Laura Inside Sales Coordinator Ms. 1/9/1958 3/5/

11th Ave. N.E. Seattle WA 98105 USA

9 Dodsworth Anne Sales Representative Ms.

Houndstoo th Rd. London

NUL

L

WG

7LT

UK

3. Create a view EmployeesUSA that displays the EmployeeID, title, full name, hire date, city, region and home phone number of all

employees working in USA. (15 pts)

7. Create a view (Manager2Group) to display the EmployeeID, FirstName and LastName of all employees whose manager is Manager

2, also display the full name of the manager 2. (15 pts)

15 points: Employees table, EmployeeID primary key

15 points: data into Employees, constraint check

15 points: EmployeesUSA view

12 points: Managers table, ManagerID primary key

12 poins: data into Managers

15 points: foreign key from Employees to Managers

15 points: view Managers2Group