I need help with my assignment I am supposed to use command prompt and sequel for it

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.

DELIVERABLE: A text file with your code and a MS Word document named LastNameF_SQLLab_CIS323_W16-17 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. Screen captures of each query results accompanied by a clear interpretation of the answer.

2. SQL code for each query accompanying the screen captures. Be sure to put each code block with its query result.

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:

1) type SQLlocalDB create

2) type SQLlocalDB start

3) type SQLlocalDB info

a) REM- note that you'll need to copy the string beginning with np returned after the command above

4) type sqlcmd -S np:\\.\pipe\LOCALDB#STRINGABOVE\tsql\query

a) Youll now have access to the SQL prompt 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 dont work flawlessly, as mentioned in class, SQL differs with various flavors and versionsbe 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

Attribute	Data Type	Primary	Foreign	Constraint
SSN Number	CHAR(9)			NOT NULL
First Name	VARCHAR(15)			NOT NULL
Mid Name	CHAR
Last Name	VARCHAR(15)			NOT NULL
Birthday	DATE
Address	VARCHAR(50)
Gender	CHAR			Gender CHECK ('M', 'F', 'm', 'f')
Salary	MONEY			DEFAULT '80000'
Supervisor SSN	CHAR(9)		employee (SSN)
Department Number	INT

Table Name: DEPARTMENT_xxx

Attribute	Data Type	Primary	Foreign	Constraint
Department Name	VARCHAR(15)			NOT NULL
Department Number	INT			NOT NULL
Manager SSN	CHAR(9)		Employee (SSN) ON DELETE SET NULL	NOT NULL
Manage Start Date	DATE

Table Name: DEPT_LOCATION_xxx

Attribute	Data Type	Primary	Foreign	Constraint
Department Number	INT		Department (DepNo) ON DELETE CASCADE	NOT NULL
Department Location	VARCHAR(15)			NOT NULL

Table Name: PROJECT_xxx

Attribute	Data Type	Primary	Foreign	Constraint
Project Name	VARCHAR(15)	UNIQUE		NOT NULL
Project Number	INT			NOT NULL
Project Location	VARCHAR(15)
Department Number	INT		Department (DepNo)

Table Name: PROJECT_ASSIGNMENT_xxx

Attribute	Data Type	Primary	Foreign	Constraint
Employee SSN	CHAR(9)		Employee (SSN) ON DELETE CASCADE	NOT NULL
Project Number	INT		Project (PNumber) ON DELETE CASCADE	NOT NULL
Hours	DECIMAL(3, 1)			NOT NULL

Table Name: DEPENDENT_xxx

Attribute	Data Type	Primary	Foreign	Constraint
Employee SSN	CHAR(9)		Employee (SSN) ON DELETE CASCADE	NOT NULL
Dependent Name	VARCHAR(15)			NOT NULL
Sex	CHAR			Gender CHECK ('M', 'F', 'm', 'f')
Birthday	DATE
Relationship	VARCHAR(8)

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

SSN	FName	Mini t	LName	BDate	Address	Sex	Salary	SuperSSN	DepNo
554433221	Doug	E	Gilbert	09-JUN-60	11 S 59 E, Salt Lake City, UT	M	80000	NULL	3
543216789	Joyce		PAN	07-FEB-78	35 S 18 E, Salt Lake City, UT	F	70000	NULL	2
333445555	Frankin	T	Wong	08-DEC-45	638 Voss, Houston, TX	M	40000	554433221	5
987654321	Jennifer	S	Wallace	20-JUN-31	291 Berry, Bellaire, TX	F	43000	554433221	4
123456789	John	B	Smith	09-JAN-55	731 Fondren, Houston, TX	M	30000	333445555	5
666884444	Ramesh	K	Narayan	15-SEP-52	975 Fire Oak, Humble, TX	M	38000	333445555	5
453453453	Joyce	A	English	31-JUL-62	5631 Rice, Houston, TX	F	25000	333445555	5
888665555	James	E	Borg	10-NOV-27	450 Stone, Houston, TX	M	55000	543216789	1
999887777	Alicia	J	Zelaya	19-JUL-58	3321 Castle, Spring, TX	F	25000	987654321	4
987987987	Ahmad	V	Jabbar	29-MAR-59	980 Dallas, Houston, TX	M	25000	987654321	4

Table Name: DEPARTMENT_xxx

DName	DepNo	MgrSSN	MgrDate
Manufacture	1	888665555	19-JUN-71
Administration	2	543216789	04-JAL-99
Headquarter	3	554433221	22-SEP-55
Finance	4	987654321	01-JAN-85
Research	5	333445555	22-MAY-78

Table Name: DEPT_LOCATION_xxx

DepNo	DLocation
1	Houston
1	Chicago
2	New York
2	San Francisco
3	Salt Lake City
4	Stafford
4	Bellaire
5	Sugarland
5	Houston

Table Name: PROJECT_xxx

PName	PNumber	Plocation	DepNo
ProjectA	3388	Houston	1
ProjectB	1945	Salt Lake City	3
ProjectC	6688	Houston	5
ProjectD	24	Bellaire	4
ProjectE	77	Sugarland	5
ProjectF	1	Salt Lake City	3
ProjectG	12	New York	2
ProjectH	34	Stafford	4
ProjectI	43	Chicago	1
ProjectJ	22	San Francisco	2

Table Name: PROJECT ASSIGNMENT_xxx

ESSN	PNo	Hours
123456789	3388	32.5
123456789	1945	7.5
666884444	3388	40.0
453453453	77	20.0
453453453	22	20.0
333445555	77	10.0
333445555	6688	10.0
333445555	43	35.0
333445555	22	28.5
999887777	1	11.5
999887777	12	13.0
543216789	22	17.0
554433221	1945	21.5

Table Name: DEPENDENT_xxx

ESSN	Dependent_Name	Sex	BDate	Relationship
333445555	Alice	F	05-APR-76	Daughter
333445555	Theodore	M	25-OCT-73	Son
333445555	Joy	F	03-MAY-48	Spouse
987654321	Abner	M	29-FEB-32	Spouse
123456789	Michael	M	01-JAN-78	Son
123456789	Alice	F	31-DEC-78	Daughter
123456789	Elizabeth	F	05-MAY-57	Spouse

3. Run Queries

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

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 6688 or project 1945.

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

10. List the name of all male employees.