TOPIC :

CAR RENTAL

I HAVE A PROVIDED A LOT OF INFORMATION YOU MIGHT NOT NEED TO USE IT. I WILL PUT THE INFORMATION IN ***

1)**************create all the tables using the information provides.(Display all the table in table format with the data type and data description ,length ,default value )****

Example

Table Name:

Attribute	Description	Key Type/ Constraint	Data Type	Filed length

********

2***********Create and execute all the SQL statements needed to add the constraints***********

3***********Create and alter the tables based on the needs ( min 5 tables maximum is 10)**************

YOU CAN USE UNION INTERSECTION AND UNION ALL OR ANY OTHER METHOD FOR MAKING THE 5 TO 10 QUERIES.

ALSO, THIS IS A MYSQL DATABASE TABLE. IHAVE MADE THE VISIO FILE FOR DESCRIBING THE DATABASE.

GIVEN BELOW ARE THE STYLES OF QUERIES WHICH U CAN USE IF U WANT TO NO COMPULSION..

-- cartesian join

select firstname,customer#,ISBN,title from customers,books;

select c.firstname,customer#,b.ISBN,b.title from customers c,books b;

select title,name from books join publisher using (pubid);

-- self join using on clause

select r.lastname,r.firstname ,c.lastname " Referred By " from

customers c join customers r

on c.REFERRED=r.CUSTOMER#;

--INNER JOIN

select title,pubid from publisher INNER join books USING (PUBID) ORDER BY PUBID;

-- RIGHT

SELECT title,pubid from publisheR RIGHT JOIN books USING (PUBID) ORDER BY PUBID;

--LEFT JOIN

SELECT TITLE,PUBID FROM BOOKS LEFT JOIN PUBLISHER USING (PUBID);

-- FULL JOIN

SELECT TITLE,PUBID FROM BOOKS FULL JOIN PUBLISHER USING (PUBID) ORDER BY 2 ;

-- Equi join LEFT(+)

SELECT C.LASTNAME ,C.FIRSTNAME ,O.ORDER# FROM CUSTOMERS C,ORDERS O

WHERE C.CUSTOMER#=O.CUSTOMER#(+);

- HAVING CLAUSE WITH IN OPERATOR

SELECT CATEGORY,MIN(RETAIL) FROM BOOKS where

CATEGORY IN ('COMPUTER','LITERATURE')

group by category HAVING MIN(RETAIL)>25;

-- rewrite the above query using where clause

select r.lastname,r.firstname ,c.lastname " Referred By " from

customers c , customers r where c.REFERRED=r.CUSTOMER#;

-- Traditional method vs join

select r.lastname,r.firstname ,c.lastname " Referred By " from

customers c , customers r where c.REFERRED=r.CUSTOMER# ;

select r.lastname,r.firstname ,c.lastname " Referred By " from

customers c join customers r on c.REFERRED=r.CUSTOMER#;

first name phane phene no Customer Employee t-Mail salesiD eustemeriD Sales Info cerID Price employeelD Car make Payment year prymentvMethod