A SQL Project I JUST FINISHED WEEK BY WEEK
Posted by mark123
Last Updated: November 03, 2012
  2406

Name: Mark James                  

WEEK 4

Exercise 1

a)create database DD_TEST_YOUR_1104306;

b)Was created with SQL GUI.

c) Create Database DD1104306_0602063

 

Exercise 2

a) The drop down shows all the databases that were created by the user.

b) USE DD_TEST_YOUR_1104306;

 

Exercise 3

What is the difference between Varchar and Char?

The char is a fixed-length character data type, the varchar is a variable-length character data type.

Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.

You can use char when the data entries in a column are expected to be the same size.
You can use varchar when the data entries in a column are expected to vary considerably in size.

What is the difference between int and numeric?

NUMERIC
Usage: NUMERIC (precision, scale)

  • Is an exact numeric type, meaning that it's a literal representation of the number’s value (Rounding or truncating of this number to meet the specified precision and scale is dictated by a predefined formula.)
  • Uses decimal precision for rounding, based on the total number of digits defined by the scale value
  • Has a total length equal to the defined precision, plus 1 if the scale is greater than 0 (for the decimal point)
  • Has a decimal portion exactly the size dictated by the scale

 

INTEGER | INT
Usage: INTEGER (precision)

  • Is an exact numeric type
  • Uses decimal precision or binary precision, which is based on the total number of binary bits used to represent the value (This is implementation-specific and will correlate with SMALLINT.)
  • Has a scale of 0 always
  • Has a minimum and maximum precision, defined by the manufacturer
  • May have a vendor-supplied default value for the precision, if no explicit value is specified

 

 

What is the difference between date and datetime?

The datetime defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock also called a timestamp, The date defines a date that is not combined with a timestamp, it just tells the date itself.

What is the difference between money and numeric?
Money is data types that is used with monetary values and numeric data types are used with values that are not currency related due to it precision component when it rounds

b).

 Create table students

(

student_id  Integer  Primary key,

firstname  Varchar(25) not null,

lastname  Varchar(25) not null,

dateofbirth date    not null,

email   Varchar(50) null,

telephone varchar(15) ,

address  Varchar(50) ,

parish  varchar(50)

);

 

 

create table book

(

book_id INTEGER Primary Key,

title  VARCHAR(50) null,

author VARCHAR(75) not null,

dateofpublication Date null

);

 

c)

insert into book

(book_id,author,title,dateofpublication)

VALUES

(1001,'Lee Holt','Green days','12/05/75'),

(1002,'Bryan Clarke','White Falls','12/10/88'),

(1003,'Mart Yee','Length and Width','10/10/78'),

(1004,'Paul Pogba','Mark Twain','12/07/77')

 

insert into students

(student_id,firstname,lastname,address,dateofbirth,parish,email,

telephone)

VALUES

(1234,'Mark','James','Dunrobin

 avenue','12/05/88','St.Ann','janoij@yahoo.com','954-5472'),

(1235,'Lee','Black','Rochester

 Avenue','10/20/70','St.James','Lolo1@live.com','881-1254'),

(1236,'Carl','Clarke','Dunrobin

 Avenue','12/12/72','St.Mary','Damekejames@live.com','456-7521'),

(1237,'David','Henry','Doncaster

 Avenue','10/10/75','St.James','Kingman12@gmail.com','774-1236')

 

d)

Select *from book

 

Select *from students

 

Exercise 4

1.  

create table testyour1104306

(

idnumber Integer   Primary Key,

Firstname Varchar(30)

);

 

create table testyour0602063

(

idnumber Integer   Primary Key,

Firstname Varchar(30)

);

2.

drop table testyour0602063

drop table testyour1104306

 

Exercise 5

 

a)

Alter table students

Add emergencycontactnumber Varchar(50),

Emergency_contact_name Varchar(50),

Emergency_contact_email Varchar(50)

 

 

b)

Alter table students

Drop column Emergency_contact_email;

 

SQL HOMEWORK WEEK 4

What is a Primary Key?

The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person). This essentially reduces the redundancy of duplicate field records in your table.

What is a Foreign Key?

A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables. A foreign key is a table column that establishes a link from the table it resides in to the primary key or a candidate key in another, related table. The foreign key is the anchor on the many side of a one-to-many (1: M) relationship, much as the primary or candidate key is the anchor on the one side of this relationship.

What is the Unique Key?

A primary key, as well as a unique constraint will uniquely identify a row in a given table. Each table can have a single primary key, and multiple unique constraints. A Primary Key cannot have a NULL value and a Unique Constraint can have a single NULL Value.

What is Entity integrity?

An entity is any person, place, or thing to be recorded in a database. Each table represents an entity, and each row of a table represents an instance of that entity. For example, if order is an entity, the orders table represents the idea of an order and each row in the table represents a specific order.

To identify each row in a table, the table must have a primary key. The primary key is a unique value that identifies each row. This requirement is called the entity integrity constraint.

What is Referential Integrity?

Referential integrity refers to the relationship between tables. Because each table in a database must have a primary key, this primary key can appear in other tables because of its relationship to data within those tables. When a primary key from one table appears in another table, it is called a foreign key.

Foreign keys join tables and establish dependencies between tables. Tables can form a hierarchy of dependencies in such a way that if you change or delete a row in one table, you destroy the meaning of rows in other tables.

 

b) Write SQL statements for the ERD done in Week 3 “Government Traffic

 Ticketing System”

 

create table driver

(

TRN Integer Primary Key,

firstName varchar(32) Not null,

middleName varchar(32) ,

lastName varchar(32) not null,

maritalStatus  varchar(10) ,

gender varchar(10) not null,

streetAddress varchar(10) ,

driverStatus  varchar(10),

ParishId Integer  null,

constraint fk_parish FOREIGN KEY(ParishId)REFERENCES

Parish(parishID)

);

create table cars

(

chassisnumber Integer Primary Key,

engineNumber Integer not null,

carYear   Integer null,

Make  Varchar(20) not null,

Model  Varchar(20) ,

Colour Varchar(20) ,

LicID Integer null,

constraint fk_licId FOREIGN KEY(licID) REFERENCES

License_Plate(licenseNumber)

);

create table License_Plate

(

licenseNumber Integer Primary Key,

dateofAssignment date  not null,

driverId  Integer null,

constraint fk_license FOREIGN KEY (driverID) 

REFERENCES driver(TRN)

);

create table Parish

(

parishID INTEGER PRIMARY KEY,

ParishName  varchar(20) ,

);

 

create table Offense

(

OffenseId Integer Primary Key,

Name  varchar(28) ,

OffenseDescription varchar(30),

Points INTEGER not null,

Offensecharge NUMERIC

);

create table ticket

(

TicketID INTEGER Primary Key,

location  varchar(25) ,

issuedatetime date not null,

duedatetime date ,

courtdatetime date not null,

Ticket_description Varchar(30) null,

OffenseId INTEGER  null

);

Create table Drives

 (

 chassisnumber Integer Not null,

 TRN  integer Not null,

 Constraint pk_cars Primary Key(chassisnumber,TRN),

 Constraint fk_drives_driver Foreign Key(TRN) References

driver(TRN),

 Constraint fk_drives__Cars Foreign Key(chassisnumber)

References cars(chassisnumber)

 );

 Create table give

(

chassisnumber Integer Not null,

 TRN  integer Not null,

 TicketID Integer Not null,

 Constraint pk_given Primary Key(chassisnumber,TRN,TicketID),

 Constraint fk_given_serve Foreign Key(chassisnumber,TRN)

references Drives(chassisnumber,TRN),

 Constraint fk_given_ticket Foreign Key(TicketID) 

references Ticket(TicketID)

);

c) Write an insert statement for each table

d) Write select statements for each table.

 

WEEK 5

Exercise 1

A)

create table driver

(

TRN Integer Primary Key,

firstName varchar(32) Not null,

middleName varchar(32) ,

lastName varchar(32) not null,

maritalStatus  varchar(10) ,

gender varchar(10) not null,

streetAddress varchar(10) ,

driverStatus  varchar(10),

ParishId Integer  null,

constraint fk_parish FOREIGN KEY(ParishId)REFERENCES

Parish(parishID)

);

create table cars

(

chassisnumber Integer Primary Key,

engineNumber Integer not null,

carYear   Integer null,

Make  Varchar(20) not null,

Model  Varchar(20) ,

Colour Varchar(20) ,

LicID Integer null,

constraint fk_licId FOREIGN KEY(licID) REFERENCES

License_Plate(licenseNumber)

);

create table License_Plate

(

licenseNumber Integer Primary Key,

dateofAssignment date  not null,

driverId  Integer null,

constraint fk_license FOREIGN KEY (driverID) REFERENCES 

driver(TRN)

);

Create table Drives

 (

 chassisnumber Integer Not null,

 TRN  integer Not null,

 Constraint pk_cars Primary Key(chassisnumber,TRN),

 Constraint fk_drives_driver Foreign Key(TRN) References

driver(TRN),

 Constraint fk_drives__Cars Foreign Key(chassisnumber)

References cars(chassisnumber)

 );

B)

create table Parish

(

parishID INTEGER PRIMARY KEY,

ParishName  varchar(20) ,

);

 

create table Offense

(

OffenseId Integer Primary Key,

Name  varchar(28) ,

OffenseDescription varchar(30),

Points INTEGER not null,

Offensecharge NUMERIC (12,3)  

);

create table ticket

(

TicketID INTEGER Primary Key,

location  varchar(25) ,

issuedatetime date not null,

duedatetime date ,

courtdatetime date not null,

paid_date_time date not null,

Ticket_description Varchar(30) null,

dateofbirth date ,

OffenseId INTEGER  null

);

Create table give

(

chassisnumber Integer Not null,

 TRN  integer Not null,

 TicketID Integer Not null,

 Constraint pk_given Primary Key(chassisnumber,TRN,TicketID),

 Constraint fk_given_serve Foreign Key(chassisnumber,TRN)

references Drives(chassisnumber,TRN),

 Constraint fk_given_ticket Foreign Key(TicketID) 

references Ticket(TicketID)

);

Exercise 2

1.

insert into Offense

(OffenseId,Name,OffenseDescription,Offensecharge,Points)

VALUES

(100,'Speeding','120km in a 50km zone',5000.54,4),

(101,'Seatbelt','Driving without Seatbelt',1000,2),

(102,'DUI','Driving under the influence',6000,5),

(103,'Seatbelt','Driving without Seatbelt',1008,2),

(104,'Broken Tail light','Brake Light Missing',2000,2)

 

insert into Parish

(parishID,ParishName)

VAlUES

(1,'Kingston'),

(2,'St.James'),

(3,'St.Ann'),

(4,'Clarendon'),

(5,'St.Catherine')

 

insert into ticket

(TicketID,location,court_date_time,due_date_time,issue_date_time,


paid_date_time,Ticket_description,OffenseId)

 VALUES

 (1201,'St.Ann','08/12/05','08/12/05','08/11/25','08/12/05',


'Speeding',100);

 

 insert into ticket

(TicketID,location,court_date_time,due_date_time,issue_date_time,

paid_date_time,Ticket_description,OffenseId)

 VALUES

 (1202,'Clarendon','09/12/01','09/11/30','09/11/19','09/11/31',


'Seatbelt',101),


 (1203,'St.Catherine','10/01/15','10/01/12','10/01/01','10/01/13',


'DUI',102),


 (1204,'Manchester','10/05/06','10/05/05','10/04/25','10/05/01',


'Seatbelt',103),


 (1205,'Clarendon','11/08/16','11/08/12','11/08/04','11/08/13',


'Speeding',104)

 

 insert into Drives

 (TRN,chassisnumber)

 VALUES

 (121450,1001),

 (121451,1002),

 (121452,1003),

 (121453,1004),

 (121454,1005)

2.

 insert into give

 (chassisnumber,TRN,TicketID)

 VALUES

 (1001,121450,1201),

 (1002,121451,1202),

 (1003,121452,1203),

 (1004,121453,1204),

 (1005,121454,1205)

 

insert into driver

 (TRN,firstName,middleName,lastName,gender,streetAddress,


maritalStatus,ParishId)

 VALUES

 (121450,'Martin','Fuller','Jenkins','Male','Dunrobin

 Avenue','Single',1),

 (121451,'Kemar','Ryan','Junes','Male','Doncaster

 Way','Married',4),

 (121452,'Justin','David','Marica','Male','Lofters

 Hall','Divorced',2),

 (121453,'Lorel','Meade','Falon','Female','Wesley

 Cresent','Single',3),

 (121454,'Marisa','Lee','Gooden','Female','Lot 5 Windows

 View','Widowed',1)

 

 insert into cars

 (chassisnumber,engineNumber,Make,Model,carYear,Colour,LicID)

 VALUES

 (1001,123456,'Toyota','Corolla',2005,'Chrome',1101),

 (1002,123457,'Mitsubishi','Lancer',2007,'Red',1102),

 (1003,123458,'Toyota','Altis',2008,'Blue',1103),

 (1004,123459,'Nissan','Fronteir',2010,'Black',1104),

 (1005,123500,'Toyota','Prado',2008,'Silver',1105)

 

 insert into License_Plate

 (licenseNumber,dateofAssignment,driverId)

 VALUES

 (1101,'07/05/28',121450),

 (1102,'08/12/25',121451),

 (1103,'05/05/20',121452),

 (1104,'09/12/12',121453),

 (1105,'10/10/25',121454)

Exercise 3

1.

insert into driver

 (TRN,firstName,lastName,gender)

 VALUES

 (121480,'Mark','James','Male'),

 (121481,'Derick','Hansley','Male'),

 (121482,'Margaret','Pryce','Female'),

 (121483,'Justine','Hardy','Female'),

 (121484,'Nadine','Henry','Female')

2.

 insert into ticket

 (TicketID,location,issue_date_time,paid_date_time)

 Values

 (1300,'St Elizabeth','08/05/12','08/05/25'),

 (1206,'Mandela Highway','05/08/25','05/09/09'),

 (1207,'Kitson Pen','10/12/27','11/01/10'),

 (1208,'Palisadoes','08/03/03','08/03/20'),

 (1209,'St.Anns Bay','08/08/25','08/09/09')

 

Exercise 4

 

1.

create table Person

 (

      Idnumber Int Identity(100,2) Primary Key,

      firstName varchar(30) ,

      lastName varchar(30)

 );

2

insert into Person(firstName,lastName)

      select Firstname,Lastname from driver

 

Exercise 5

1.

create table dummy_driver

(

TRN Integer Primary Key,

firstName varchar(32) Not null,

middleName varchar(32) ,

lastName varchar(32) not null,

maritalStatus  varchar(10) ,

gender varchar(10) not null,

streetAddress varchar(50) ,

driverStatus  varchar(10),

dateofbirth date  ,

ParishId Integer  null,

);

insert into dummy_driver

 select * from driver

 

 Update dummy_driver

 SET driverStatus='Active'

 

2.

Update dummy_driver

SET lastName='Wise'

Where TRN=121450

3.

Update Offense

SET Points=6

Where Name='Seatbelt'

4.

Update cars

SET Make='Nissan',Model='Frontier',colour='Chrome'

Where Make='Toyota'

5.

Update Offense

SET Offensecharge=2500

Where Points<6

6.

Update driver

Set firstName='John',lastName='Smith',gender='Male'

Where TRN=121505

 

 

Exercise 6

1.

Delete from dummy_driver

where ParishId=1

2.

Delete from dummy_driver

where lastName='James'

 

3.

Delete from dummy_driver

where lastName='Brown' OR ParishId=

 

SQL HOMEWORK WEEK 5

 

select * from driver

 

select chassisnumber,make From Cars

 

select * from Offense

where Points>4

 

Select * from Offense

ORDER by Points DESC

 

Select * from Offense

Order by Points,Name

 

SELECT DATEDIFF(year,dateofbirth,GETDATE())AS Age From driver

 

SELECT AVG(Points) AS OffenseAverage FROM Offense

 

SELECT MAX(Points)AS MAXPOINTS FROM Offense

 

Select MIN(Points)  AS MINPOINTS FROM Offense

 

SELECT ParishId, Count(ParishId) AS SUMOFPARISH

FROM driver

GROUP BY ParishId

WEEK 6

Exercise 1

1.

Select * from driver

 

Select *from Parish

 

Select *from ticket

2.

Select * from cars

 

Select * from License_Plate

 

Select *from Offense

 

EXERCISE 2

1.

Select TRN,firstName,LastName From driver

 

Select ParishName from Parish

 

Select ticketId, Location, Ticket_description from ticket

 

Select chassisnumber,make,Model,Colour From cars

 

Select driverId,LicenseNumber From License_Plate

 

Select OffenseId,Points,Name From Offense

 

2.

 

Select firstName,LastName, TRN from driver

 

Select ParishID,ParishName from Parish

 

Select Location,ticketID,Ticket_description from ticket

 

Select make,model,Colour,chassisnumber from cars

 

Select LicenseNumber,driverId from License_Plate

 

Select Name,Points,OffenseId from Offense

 

EXERCISE 3

A.

select * from Offense

where Points>4

B

Select * from Offense

Where Offensecharge>=10000

 

C

Select *from driver

Where ParishId=1

D

Select *from Offense

Where Points Between 2 And 4

 

E

Select firstName,LastName from driver

Where ParishId=1

F

Select Firstname,LastName from driver

Where ParishId=5 AND NOT (lastName='Brown')

EXERCISE 4

A

Select *from driver

Order by dateofbirth DESC,lastName ASC,Firstname DESC

B

Select * from cars

Order by carYear DESC,Make DESC,model ASC

C

Select Firstname,LastName,ParishId,MaritalStatus from driver

Order by ParishId DESC,lastName ASC,firstName ASC

D

Select name,Offensecharge from Offense

Order by Points ASC,Offensecharge DESC

EXERCISE 5

1

Select *,DATEDIFF(YEAR,dateofbirth,GETDATE()) AS AGE from driver

2

Select *,(Offensecharge*0.15) AS OffenseTAX From Offense

EXERCISE 6

1

Select * from driver

Where lastName Like'S%'

2

Select *from cars

Where Make Like'H%'

3

Select * from Offense

Where Name Like'%op%'

4

Select * from Parish

where ParishName Not Like '%St%'

EXERCISE 7

1

Select AVG(offensecharge)AS AVERAGEOFFENSE From Offense

2

Select MAX(offensecharge)AS MAXOFFENSE From Offense

3

Select MIN(offensecharge)AS AVERAGEOFFENSE From Offense

4

Select COUNT(offensecharge) AS OFFENSECOUNT From Offense

5

Select SUM(offensecharge) AS TOTALOFFENSE from Offense

6

Select OffenseId, AVG(offensecharge) AS CHARGEPEROFFENSE 

from Offense

GROUP BY OffenseId

 

 

EXERCISE 8

1

Select ParishId,COUNT(parishId)AS ParishCount from driver

Group by ParishId

2

Select Points, COUNT(Points) AS OFFENSECOUNT from Offense

Group by Points

3

Select maritalStatus,COUNT(maritalStatus) AS Marriagestatuscount

from driver

Group by maritalStatus

4

Select Caryear,COUNT(CarYear) AS CARYEARCOUNT from cars

GROUP by carYear

WEEK 7

EXERCISE 1

.1

Select TRN,firstName,lastName,gender,maritalStatus,ParishName 

from driver

INNER JOIN Parish

ON driver.ParishId=Parish.parishID

.2

Select *, name,Points,offensecharge from ticket

INNER JOIN Offense

On Ticket.OffenseId=Offense.OffenseId

.3

Select driver.TRN,firstName,Lastname,issue_date_time,due_date_time,

paid_date_time from ticket

INNER JOIN give

ON ticket.TicketID=give.TicketID

INNER JOIN Driver

ON driver.TRN=give.TRN

 

.4

Select driver.TRN,firstName,Lastname,issue_date_time,due_date_time,

paid_date_time from ticket

INNER JOIN give

ON ticket.TicketID=give.TicketID

INNER JOIN Driver

ON driver.TRN=give.TRN

where gender='Female'

 

.5

Select cars.chassisnumber,make,model,carYear,LicenseNumber,TRN,firstname,

LastName from cars

INNER JOIN License_Plate

ON cars.LicId=License_Plate.licenseNumber

INNER JOIN Driver

ON License_Plate.driverId=Driver.TRN

.6

Select cars.chassisnumber,make,model,carYear,LicenseNumber,TRN,firstname,

LastName from cars

INNER JOIN License_Plate

ON cars.LicId=License_Plate.licenseNumber

INNER JOIN Driver

ON License_Plate.driverId=Driver.TRN

Where carYear>=2007

.7

Select give.TRN,Points,SUM(Points) AS TOTALPOINTSPERDRIVER from give

INNER JOIN ticket

ON give.TicketID=ticket.TicketID

INNER JOIN Offense

ON ticket.OffenseId=Offense.OffenseId

GROUP BY Points,TRN

 

EXERCISE 2

 

.1

SELECT LicenseNumber,dateofAssignment,TRN, firstName,LastName 

from License_Plate

INNER JOIN driver

ON License_Plate.driverId=driver.TRN

.2

SELECT LicenseNumber,dateofAssignment,TRN, firstName,LastName 

from driver

Left Join License_Plate

ON License_Plate.driverId=driver.TRN

.3

SELECT driver.TRN,firstName,LastName,driver.ParishId,

ParishName from driver

Left JOIN Parish

ON driver.ParishId=Parish.parishID

WHERE driver.ParishId is Null

.4

Select offense.OffenseId,name,TicketID

from ticket

LEFT JOIN Offense

ON Offense.OffenseId=ticket.OffenseId

Where Offense.OffenseId is null and Name is null

 

EXERCISE 3

.1

SELECT LicenseNumber,dateofAssignment,TRN, firstName,LastName from driver

RIGHT Join License_Plate

ON License_Plate.driverId=driver.TRN

 

SELECT LicenseNumber,dateofAssignment,TRN, firstName,LastName 

from License_Plate

RIGHT JOIN driver

ON License_Plate.driverId=driver.TRN

 

SELECT driver.TRN,firstName,LastName,driver.ParishId,

ParishName from Parish

RIGHT JOIN driver

ON driver.ParishId=Parish.parishID

WHERE driver