# Some SQL help, please =/



## xazraelx (Apr 11, 2008)

Okay, well, I am a COMPLETE newbie when it comes to anything related to SQL.  Anyway, I'm confused heavily...Here's what my assignment is supposed to be.

Write some sql to create a database described in the book (did that, pretty much used the book as a reference...I tried uploading it to some server our teacher gave us, and executing, but it gives me a ton of errors and no clue why)

```
CREATE TABLE EMPLOYEE
(
	Fname 		VARCHAR(15) 		NOT NULL,
	Minit 		CHAR, 
	Lname 		VARCHAR(15) 		NOT NULL,
	Ssn 		Char(9) 			NOT NULL,
	Bdate 		DATE,
	Address 	VARCHAR(30),
	Sex 		CHAR,
	Salary 		DECIMAL(10,2),
	Super_ssn 	CHAR(9),
	Dno INT 						NOT NULL,
	PRIMARY KEY(Ssn),
	FOREIGN KEY(Super_ssn) REFERENCES EMPLOYEE(Ssn),
	FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber) 
);
CREATE TABLE DEPARTMENT
(
	Dname		VARCHAR(15)			NOT NULL,
	Dnumber		INT					NOT NULL,
	Mgr_ssn		Char(9),
	Mgr_start_date	Date,
	PRIMARY KEY(Dnumber),
	Unique(Dname),
	FOREIGN KEY(Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
);
CREATE TABLE DEPT_LOCATIONS
(
	Dnumber		INT 				NOT NULL,
	Dlocation	VARCHAR(15)			NOT NULL,
	PRIMARY KEY(Dnumber, Dlocation),
	FOREIGN KEY(Dnumber) REFERENCES DEPARTMENT(Dnumber)
);
CREATE TABLE PROJECT
(
	Pname		VARCHAR(15)			NOT NULL,
	Pnumber		INT					NOT NULL,
	Plocation	VARCHAR(15),
	Dnum		INT					NOT NULL,
	PRIMARY KEY(Pnumber),
	UNIQUE(Pname),
	FOREIGN KEY(Dnum) REFERENCES DEPARTMENT(DnumbeR)
);
CREATE TABLE WORKS_ON
(
	Essn		CHAR(9)				NOT NULL,
	Pno			INT					NOT NULL,
	Hours		DECIMAL(3,1)		NOT NULL,
	PRIMARY KEY(Essn, Pno),
	FOREIGN KEY(Essn) REFERENCES EMPLOYEE(Ssn),
	FOREIGN KEY(Pno) REFERENCES PROJECT(Pnumber)
);
CREATE TABLE DEPENDENT
(
	Essn		Char(9)				NOT NULL,
	Dependent_name VARCHAR(15)		NOT NULL,
	Sex			CHAR,
	Bdate		DATE,
	Relationship VARCHAR(8)
	PRIMARY KEY(Essn, Dependent_name),
	FOREIGN KEY(Essn) REFERENCES EMPLOYEE(Ssn)
);
```

After that, I'm suppose to write some queries...here are two that I've tried to write so far.


```
SELECT Fname, Lname 
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = 'Human Resources';

SELECT Fname, Lname
FROM EMPLOYEE, PROJECT
WHERE Pname = 'Forbin';
```

I am kind of confused how I would go about writing a query for listing the name of EACH project as well as some information about the project.

In addition, I have no idea how to "Your project should drop all tables (with cascade) at the end of the script."

Thanks TPU, it is MUCH appreciated.


----------



## Disparia (Apr 11, 2008)

If you wanted everything from projects:

```
SELECT * FROM projects
```
Some information might be in other tables:

```
SELECT Pname, Ptype FROM projects JOIN types on project.typeid = type.typeid
```
More complex queries might use left/right, inner/outer joins, group bys, etc.

As for your table question... I usually just use SQL Studio for MSSQL or phpMyAdmin for MySQL for my database/table creation so I won't be much help there, sorry.

If you know which database you're using, look up the syntax for table creation.


----------



## Kreij (Apr 11, 2008)

What database are you using?
It makes a difference.

For instance, I believe that SQL Server doesn’t support the “CASCADE CONSTRAINTS” clause with the DROP command, so the referential constraints would have to be dropped before the tables.

MO INFO !!


----------

