SQL Lab Assignment

This assignment counts as one lab in the LAB category of your grade & is worth 100 points. This is an INDIVIDUAL assignment. Copying other’s code or sharing answers will result in a 0 on the assignment at a minimum and will constitute an academic integrity violation for all parties.

DUE DATE:
BEFORE 11 p.m., Thursday, Feb 13.

Assignments are to be completed in SQL Server Express (SQLlocalDB.exe & SQLcmd.exe) and the deliverable (see below) submitted to Moodle in a Word document before the drop box closes.

There are three tasks in this assignment: A)
create tables, B)
insert data, and C) complete queries.
For each task, you need to write the SQL code and execute it in your database you will create using the SQL Server Express commandline tools installed on the lab computers.

DELIVERABLES:
A text file (.txt) with your code AND a MS Word document (.docx), both named

LastnameF_SQLLab_CIS323_W20

(-5 if not correct for both files) are to be uploaded into Moodle before the deadline. The documents must be neatly formatted and logically organized (this will be part of the grading criteria) and should contain the following:

1. Your text file should contain all of your SQL code (table creation, data insertion, and queries)

2. Your MS Word document should contain screen captures (CROPPED) of each query with the query’s results. You will also need to include a typed, clear interpretation of the answer (this is a statement indicating in your own words what your query syntax does and why it was selected).

TO ACCESS SQL Tools:

FIRST

you will need to open the command line window (on anywhere.latech.edu or lab computers). Once the window is opened (access the Command Prompt) then use the following directions to get to an SQL prompt (note: do not type in the quotes and change yourinitials to your individual initials):

1) type “SQLlocalDB create CIS323_yourinitials”

2) type “SQLlocalDB start CIS323_yourinitials”

3) type “SQLlocalDB info CIS323_yourinitials”

a) note: you'll need to copy the text beginning with “np…” returned after the “info” command above

4) type “sqlcmd -S
np:\\.\pipe\LOCALDB#TEXTABOVE\tsql\query” (pasting in this section the copied “np….” text)

a) You’ll now have access to the SQL prompt (1>) and able to create and modify tables and questions

NOTES:

• Be aware of typing in commands exactly as indicated but do not be surprised if they don’t work flawlessly, as mentioned in class, SQL differs with various flavors and versions…be persistent and troubleshoot when needed.

• BE RESOURCEFUL: Great places to visit for additional information or to seek advice are
www.stackoverflow.com

or
www.w3schools.com

1.


Create Tables - NOTE:

Replace the “xxx” with your initials in lowercase.

Table Name: EMPLOYEE_xxx

Table Name: DEPARTMENT_xxx

Table Name: DEPT_LOCATION_xxx

Table Name: PROJECT_xxx

Table Name: PROJECT_ASSIGNMENT_xxx

Table Name: DEPENDENT_xxx

Hint
: you have two ways to define foreign key, one is to define foreign key within the CREATE TABLE statement, for example:

CREATE TABLE products

(product_id numeric(10) not null, supplier_id numeric(10), CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE SET NULL)

Another way is to create table without defining foreign key and add foreign key later using the ALTERTABLE statement (sometimes you might have to do like this), for example
:

ALTER TABLE Products ADD CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE SET NULL

2. Insert Data

Table Name: EMPLOYEE_xxx

Table Name: DEPARTMENT_xxx

Table Name: DEPT_LOCATION_xxx

Table Name: PROJECT_xxx

Table Name: PROJECT ASSIGNMENT_xxx

Table Name: DEPENDENT_xxx

3. Run Queries

1. List the names only of all employees who work in department 5.

2. List names and salaries of all employee ordered by salary.

3. List the name of employees whose salary is between 30000 and 50000.

4. List the name and address of employees who live in Houston.

5. List the name of employees with no supervisor.

6. List department number and number of employees in each department, ordered by number of employees in each department.

7. List department number and number of employees in departments that have more than 2 employees, ordered by department number.

8. List the ESSN of employees who works on project 3388 or project 22.

9. List the location of department 2, 4 and 5.

10. List the name of all female employees.