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

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=3 

 

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.ParishId is Null

 

Select offense.OffenseId,name,TicketID

from Offense

Right JOIN ticket

ON Offense.OffenseId=ticket.OffenseId

Where Offense.OffenseId is null and Name is null

 

EXERCISE 4

.1

insert into cars

(chassisnumber,engineNumber,Make,Model,Colour)

VALUES

(1800,6500,'Honda','CRV','Red'),

(1801,6501,'Honda','Accord','Silver')

 

Insert into License_Plate

(licenseNumber,dateofAssignment,driverId)

VALUES

(1350,'08/05/08',NULL);

 

.2

SELECT * from driver

FULL JOIN License_Plate

ON driver.TRN=license_Plate.driverId

FULL JOIN cars

ON License_Plate.licenseNumber=cars.LicID

Where License_Plate.driverId

IS not null AND cars.LicID Is not null

 

WEEK 8

EXERCISE 1

.1

Select d.firstname,d.lastname,Licensenumber from driver d

INNER JOIN(Select * from License_Plate where Licensenumber

IN(1101,1102) )L

ON d.TRN=L.driverId

 select * from driver

.2

Select t.ticketId,d.firstName,d.LastName,d.TRN from ticket 

t

INNER JOIN give g

ON t.TicketID=g.TicketID

INNER JOIN(select *from driver where lastName='Jenkins')d

ON g.TRN=d.TRN

 

.3

insert into Drives

(TRN,chassisnumber)

VALUES

(121505,1800);

 

insert into give

(TRN,chassisnumber,TicketID)

VALUES

(121505,1800,1210);

 

 

Select t.*,d.*from ticket t

INNER JOIN give g

ON t.TicketID=g.TicketID

INNER JOIN(select *from driver where lastName Like 'S%' 

AND ParishId=1)d

ON g.TRN=d.TRN

 

.4

Select d.firstName,d.Lastname,t.ticketId,O.Offensecharge

from driver d

INNER JOIN give g

ON d.TRN=g.TRN

INNER JOIN ticket t

ON g.TicketID=t.TicketID

INNER JOIN (Select * from Offense where Offensecharge >5000) O

ON t.OffenseId=O.OffenseId

 

EXERCISE 2

.1

Select d.Trn,firstName,LastName from driver d

where d.TRN IN

(Select TRN from give g where g.TicketID IN

(Select TicketID from ticket t where t.OffenseId IN

(Select OffenseId from Offense O where O.Offensecharge> 5000)))

 

.2

 

Select t.* from ticket t where t.TicketID IN

(Select ticketId from give g where g.TRN IN

(Select TRN from driver d where d.lastName='Jenkins'))

 

.3

insert into License_Plate

 VALUES

 (1500,'03/03/09',121505);

 insert into cars

 VALUES

 (1250,13300,2009,'Toyota','Celica','Blue',1500);

 

Select c.* from cars c where c.LicID IN

 (Select LicenseNumber from License_Plate l where l.driverId IN

 (Select TRN from driver d where d.lastName

IN(‘Smith’,’Doe’,’Brown’)))

 

EXERCISE 3

.1

ALTER TABLE driver

 ADD CarBorrowID INTEGER

 

 UPDATE driver

 SET CarborrowID=121450

 WHERE maritalStatus='Widowed'

 

 select *from driver

 Select d.*,d1.* from driver d

 INNER JOIN driver d1 ON d1.CarBorrowID=d.TRN

 

SQL HOMEWORK WEEK 8

What is the Exists condition?

The Exists condition is the process of using a sub query to test for the existence of rows. If it is true, The Exists operators return a row, then the outer query proceeds. If not, the outer query does not execute or return any result or row.

What is the Not Exists operator?

When a NOT operator is combined with an EXISTS condition. This is the opposite of the Exists condition in that, the result of an EXISTS condition is true if the sub query resolves to at least one row. The result of a NOT EXISTS condition is true if the sub query evaluates to zero rows.

What is a Union operator?

The Union operator is used to combine the result-set of two or more SELECT statements. By the default the Union operator usually selects distinct values.

What is the Intersect operator?

The intersect operator is used to retrieve the common records from both the left and the right query of the Intersect Operator. When using INTERSECT operator the number and the order of the columns must be the same in all queries as well data type must be compatible.

What is the Difference Operator?

 

What is the Cartesian product Operator?

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join. It is causes by specifying a table in the FROM clause without joining it to another table.

What is the Except Operator?

The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows which are not available in second SELECT statement.

What is the All Operator?

The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, ! =, >, <, <=, >= and followed by a list or subquery. When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with AND operators,

What is the Some Operator?

SOME compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row. SOME must match at least one row in the subquery and must be preceded by comparison operators. E.g.  using greater than ( >) with SOME means greater than at least one value.

Examples

Union Scenario

I have a business in St. Ann and Kingston. Each business has five staff members; however one of my staff is a Supervisor for both stores. Therefore his Id number will be stored at both stores. If I want to view the results of all the different employees at both stores I would implement a query like this:

SELECT E_Fname FROM  StANN_employee

UNION

SELECT E_Fname FROM  Kingston_employee

This query would output 9 employees why? This is due to the fact that the Union works with distinct values and since a Supervisor controls both stores the Union recognises the record as one. If we were to get 10 we would have to use the Union ALL operator.

Intersect Scenario

In my business I would like to see my employee’s employment history from my HR. A query to implement this would be:

SELECT *
FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN (1,2,3)
INTERSECT
SELECT *
FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN (3,2,5)

From the above info we can see that the query will display information for EmployeeID 2 and 3, because there is an intersection or common field of these numbers in both tables.

Cartesian Product Scenario

 The cartesian product of {a,b,c} and {c,d,e} is {(a,c),(a,d),(a,e),(b,c),(b,d),(b,e),(c,c),(c,d),(c,e)}.

The Cartesian  product is not concerned with any tuples greater than 2. Therefore for a Query to check Table A and B like this:

SELECT *from a, b results in the Cartesian product from rows in a table and rows in b table.

select *from driver

 Select d.*,d1.* from driver d

 INNER JOIN driver d1 ON d1.CarBorrowID=d.TRN

WEEK 9

Exercise 1

.1

UPDATE driver

 SET dateofbirth='12/05/75'

 Where maritalStatus='Single'

 

 UPDATE driver

 SET dateofbirth='11/05/82'

 Where maritalStatus='Married'

 

 

 UPDATE driver

 SET dateofbirth='08/05/87'

 Where maritalStatus IS NULL

 

 UPDATE driver

 SET dateofbirth='09/25/84'

 Where lastname ='Marica'

 

 UPDATE driver

 SET dateofbirth='09/25/86'

 Where lastname ='Henricks'

 

 Select firstName,LastName,dateofbirth,

 DATEDIFF(year,dateofbirth,GETDATE())AS AGE 

 from driver

 

.2

Select *,DATEDIFF(year,dateofbirth,GETDATE())AS AGE

from driver

where DATEDIFF(year,dateofbirth,GETDATE())>25

 

.3

Select *,DATEDIFF(year,dateofbirth,GETDATE())AS AGE

from driver

where DATEDIFF(year,dateofbirth,GETDATE())>25 AND DATEDIFF(year,dateofbirth,GETDATE())<30

.4

Select *,DATEDIFF(year,dateofbirth,GETDATE())AS AGE

from driver

where DATEDIFF(QUARTER,dateofbirth,GETDATE())IN(3,4)

 

EXERCISE 2

1.

Select *,DATEADD(year,5,'11/3/2012')AS DATEINCREMENTAGE,

 DATEDIFF(year,dateofbirth,GETDATE()) AS AGE

from driver where DATEDIFF(year,dateofbirth,GETDATE()) >=30

 

2.

Select d.*,g.ticketID,t.OffenseID,DATEADD(year,5,'11/3/2012')AS

DATEINCREMENTAGE

from driver d

INNER JOIN give g

ON d.TRN=g.TRN

INNER JOIN ticket t

ON g.TicketID=t.TicketID

INNER JOIN Offense O

ON t.OffenseId=O.OffenseId

Where Offensecharge IS NOT NULL AND

DATEDIFF(year,dateofbirth,GETDATE()) >=30

 

EXERCISE 3

 

.1

create table LEARNERS

(

Id Int  Not null  Primary Key,

LastName varchar(255) Not NULL,

FirstName varchar(255) Not NULL,

Marital_Status char(1) Default 'S',

Address varchar(255),

Email varchar(255) ,

Parish varchar(50),

Constraint chk_LEARNERS CHECK(id>0 AND Parish

in('Kingston','St.Catherine','Portland','St. Andrew')),

Constraint chk_Person_Marital Check(Marital_status='S' 

OR Marital_Status ='M'),

constraint uk_email Unique(Email),

);

.2

insert into LEARNERS

 VALUES

 (10025,'James','Maria','S','Manchester Way',NULL,'Manchester');

 

 --First there was an error related to having two Primary key

 constraints. One was removed to correct this problem

 --When record was inserted that included Manchester ,

There was an error message displayed saying:

 --The INSERT statement conflicted with the CHECK constraint

 "chk_LEARNERS". The conflict occurred in database

 "DDWEEKS1104306_0602063", table "dbo.LEARNERS".

 

.3

insert into LEARNERS

 (Id,FirstName,LastName)

 VALUES

 (10245,'Jimmy','Fallon');

 

 select *from LEARNERS

 --The value S was placed in the field instead of a NULL value

 because of the DEFAULT constraint.

 

.4

insert into LEARNERS

 (Id,FirstName,LastName,Email)

 VALUES

 (12345,'Rowena','Francis','janoij@yahoo.com'),

 (10254,'Tamara','Higgins','janoij@yahoo.com')

 

 --An error message appeared describing the:

 --Violation of UNIQUE KEY constraint 'uk_email'. Cannot insert

 duplicate key in object 'dbo.LEARNERS'

EXERCISE 4

.1

Select  Distinct(parishID) from driver

.2

 Select Distinct(firstName) from driver

.3

Select Distinct (Make,Model) from cars

EXERCISE 5

.1

Insert into Person

 (firstName,lastName)

 VALUES

 ('Cindy','Walker'),

 ('Lorena','Phillips'),

 ('Craig','Dunbar')

.2

Select firstName,LastName from Person

 UNION

 Select firstName,LastName from driver

--No records were duplicated when using the UNION between both

 tables However

 --he records that were entered did not show up in the UNION.

.3

Select firstName,LastName from Person

 UNION

 Select firstName,LastName from driver where gender='Male'

.4

Select firstName,LastName from Person

 UNION

 Select firstName,LastName from driver where gender='Female'

.5

  Select firstName,LastName, 'Person' AS Source from Person

 UNION

 Select firstName,LastName, 'driver' AS Source from driver

SQL HOMEWORK WEEK 9

What are views?

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

How are views different from tables?

The view is a virtual table that can contain record from one or more real tables in your database, a view is also reliant on the result set of a SQL statement.

Develop the SQL statement to create a view that only shows the TRN, first, last Name of drivers.

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE VIEW Driver_List AS

SELECT TRN, Firstname, Lastname

FROM driver

WHERE driver.TRN=ticket.driverID

To Display Query: SELECT * FROM  Driver_List;

 

Develop the SQL statements that show Persons that have tickets using the view instead of Driver Table in a JOIN statement.

SELECT gives.ticketID, firstname, lastname, TRN

FROM Driver_Listing

INNER JOIN gives ON Driver_Listing.TRN=Gives.DriverID
Default Image

Samath wow! by the time you finish with this course you are an expert!

Related Content