[go: up one dir, main page]
Scribd
Upload
201 views6 pages

CS512 DBA Assignment 1

This document outlines assignment questions for a database administration course involving writing SQL queries and creating database tables. It provides 5 SQL SELECT questions to retrieve and display data from an Employees table. It also provides instructions to design tables for a customer purchase database and include foreign key constraints. Students are instructed to print their solutions and submit them by a specified deadline.

Uploaded by

Rami Daggolu
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
201 views6 pages

CS512 DBA Assignment 1

This document outlines assignment questions for a database administration course involving writing SQL queries and creating database tables. It provides 5 SQL SELECT questions to retrieve and display data from an Employees table. It also provides instructions to design tables for a customer purchase database and include foreign key constraints. Students are instructed to print their solutions and submit them by a specified deadline.

Uploaded by

Rami Daggolu
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

Assignment #1

Course CS512 DBA [2011 Summer]


Student Number
Family Name
First Name

To: Prof R. Liang


Problem A SQL SELECT

Given a table definition:

Employees( employee_id, first_name, last_name, salary,


manager_id, hire_date, commission_pct, department_id)

write the following SQL statements.

A.1 Write a query that displays the first eight characters of the employees’ last names and
indicates the amounts of their salaries with asterisks. Each asterisk signifies a thousand
dollars. Sort the data in descending order of salary.

A.2 Write a query to display the manager number and the salary of the lowest-paid
employee for that manager. Exclude anyone whose manager is not known. Exclude any
groups where the minimum salary is $6,000 or less. Sort the output in descending order
of salary.
A.3 Write a query to display employees’ last names and employee number along with
their managers’ last names and manager number.

A.4 The HR department wants to determine the names of all employees who were hired
after Davies. Create a query to display the name and hire date of any employee hired after
employee Davies.
A.5 Display the employee number, last name, and salary of all employees who earn more
than the average salary and who work in a department with any employee whose last
name contains a “u”.
Problem B DDL

Assume that you are designing a database with the following tables:

CUSTOMER (CustomerID, LastName, FirstName, Phone, Email)


PURCHASE (InvoiceNumber, Date, PreTaxAmount, CustomerID)
PURCHASE_ITEM (InvoiceNumber, ItemNumber, RetailPrice)
ITEM (ItemNumber, Description, Cost, ArtistName)

where

CustomerID in PURCHASE must exist in CustomerID in CUSTOMER


InvoiceNumber in PURCHASE_ITEM must exist in InvoiceNumber in PURCHASE
ItemNumber in PURCHASE_ITEM must exist in ItemNumber in ITEM.

Write CREATE TABLE statements for each of these tables. Include any foreign key
constraints appropriate for each of these tables. Make your own assumptions regarding
cascading deletions.
Deliverables

Due: by 11AM Saturday 05/28/2011.

Hand in a hardcopy of your assignment. Print out your work if possible. Otherwise hand-
written is fine, but use pen, not pencil. Do not use binders, folders, paper clips etc. Use
standard size paper (8.5" x 11"), and have the sheets stapled together.

Have a cover page for submitting your work, filling out your student number, and name.

Please hand in your assignment to Prof. R. Liang, before 11am, Saturday 05/28/2011.
Late assignments will not be accepted.

You might also like