Introduction to SQL using Access
Week 1
- SELECT DISTINCT Email
FROM Customer; - SELECT FirstName, LastName, State
FROM Customer
ORDER BY State ASC; - SELECT Firstname
FROM Customer
ORDER BY FirstName DESC;
Explanation: Whilst this query runs OK, it is good practice to capitalise field-names consistently. - SELECT FirstName, LastName
FROM Customer
ORDER BY LastName;
Explanation: There was a syntax error in this query - a missing comma separating the field_names (now added in!). - SELECT * FROM
Customer ORDER BY
Email DESC;
Explanation: Whilst this query works fine, it is more easily read if the keywords are on a separate line, as follow:
SELECT *
FROM Customer
ORDER BY Email DESC;
- SELECT FirstName, LastName, Address, City, State, Zip, Country
FROM Customer
ORDER BY Zip;
- This database will store details of Trycycling in Edinburgh bicycle rides for new & non-confident cyclists, as well as details of the adults & children who take part in the monthly rides.
Table Name: InsurancePolicy Customer Dependent Fields: EffectiveDate
Duration
CustomerID
PolicyID (PK)
StatusFirstName
LastName
Address
DoB
CustomerID (PK)FirstName
LastName
DependentID (PK)
DoB
InsuredIDKey: (PK) = Primary Key = Foreign Key
Table Name: | CycleRide | AdultCyclist | ChildCyclist |
---|---|---|---|
Fields: | RideID (PK) RideDate Duration StartTime EndTime StartingAt Destination AdultID |
AdultID (PK) FirstName LastName Address1 Address2 Town/City PostCode AgeRange |
ChildID (PK) FirstName LastName Age AdultID |
Key: | (PK) = Primary Key = Foreign Key |
Week 2
- Show first names & last names of customers living in California
SELECT FirstName, LastName
FROM Customer
WHERE State = 'CA'; -
Show last names & email addresses of customers living in the USA & want to receive promotion email.
SELECT LastName, Email
FROM Customer
WHERE Country = 'USA' AND PromoEmail = Yes; - Show customers whose first names start with letter M
SELECT *
FROM Customer
WHERE FirstName LIKE 'M*';
- Show customers living in the USA & don't have the phone number
SELECT *
FROM Customer
WHERE Country = 'USA' AND Phone IS NULL;
- Show customers living in California & customers living in Texas
SELECT *
FROM Customer
WHERE State = 'CA' OR State = 'TX';
- Show payments paid by Visa card and the price is higher than $100
SELECT *
FROM Payment
WHERE CreditCard = 'VISA' AND Price > 100;
- Show customer ID of those who bought Sony CPU
SELECT CustID
FROM Payment
WHERE Description = 'Sony CPU';
- Show payments paid in 2005 and first quarter of 2006
SELECT *
FROM Payment
WHERE PaidDate LIKE '*2005' OR PaidDate BETWEEN #01/01/2006# AND #31/03/2006#;
- Write a query using AND & provide explanation. If you use your own table, show the screen shot of your table in Datasheet View.
SELECT FirstName, LastName, Email
FROM Customer
WHERE Country <> 'USA' AND PromoEmail = Yes;
This query finds the names & email addresses of all customers from outwith the USA who have opted to receive promotional material, presumably to target them with a mailshot! The AND clause ensures that both conditions are met. - Write a query using NOT IN and provide explanation.
SELECT *
FROM Customer
WHERE State NOT IN ( 'CA') OR State IS NULL;
This query finds the details of all customers who do not live in California. The NOT IN clause found most of them, but did not return customers from abroad with a NULL value in the State field; An extra OR condition had to be added to return these customers.
Week 2: Optional Assignment
- Is "Cable HS300" on the result list of the below query?
Why/Why not?
SELECT *
FROM Sales
WHERE product LIKE '*[1-3]';
Cable HS300 doesn't meet the specified criteria: The * covers all the unspecified letters "Cable HS" that preceed the number, and the number "3" is covered by the range [1-3] but the "00" fall outwith the specified range, so "Cable HS300" is not returned as a result. - Is "Cable HS140" on the result list of the below query?
Why/Why not?
SELECT *
FROM Sales
WHERE product LIKE 'Cable*[14]';
Cable HS140 does not meet the specified criteria: "Cable HS14" is represented by the *, however the 0 does not fall within the range [14], so is not returned in the result. - Is "ACable HS140" on the result list of the below query?
Why/Why not?
Is "TCable HS140" on the result list of the below query?
Why/Why not?
SELECT *
FROM Sales
WHERE product LIKE '[!Cable]*';
"TCable HS140" is on the result list, but "ACable HS140" is not. In this query "[Cable]" is part of a specified set, & "HS140" are represented by the "*", the "!" matches any character other than those in the specified set, as "A" is in the specified set, "ACable HS140" does not meet the specified criteria & so isn't returned in the result; however, "T" is not in the specified set, & so "TCable HS140" meets the criteria & is returned in the result. - Write a query for the Sales table using [ ] or [! ] and explain the query.
Week 3
- Find out how many customers want to receive promotional emails and have email addresses in their records.
SELECT COUNT(CustID)
FROM Customer WHERE Email IS NOT NULL AND PromoEmail = Yes; - Find out how much money is paid in the first quarter of 2006
SELECT SUM(Price)
FROM Payment
WHERE PaidDate >= #01/01/2006# AND PaidDate <= #31/03/2006#; - Find out how many payments that are more than $50
SELECT COUNT(PaymentID)
FROM Payment
WHERE Price > 50;
- Find out the average payments paid in 2005
SELECT AVG(Price)
FROM Payment
WHERE PaidDate >= #01/01/2005# AND PaidDate <= #31/12/2005#;
- Find out the highest payment in 2005
SELECT MAX(Price)
FROM Payment
WHERE PaidDate >= #01/01/2005# AND PaidDate <= #31/12/2005#;
- Write a query joining 2 tables. Provide explanation for your query. If you use your own tables, show the screen shots of your tables in Database View.
SELECT C.FirstName, C.LastName, CP.OnlineShopper
FROM Customer C, CustomerProfile CP
WHERE C.CustID = CP.CustID AND CP.OnlineShopper = Yes
ORDER BY C.LastName ASC;
This query selects the first & last names of customers who are online shoppers, ordering them by last name ascending - Write a query using GROUP BY. Provide explanation for your query. If you use your own tables, show the screen shots of your tables in Database View.
SELECT CreditCard, SUM(Price)
From Payment
GROUP BY CreditCard
HAVING SUM(Price) > 2000;
This query selects the credit cards by group (GROUP BY), totalling the amounts spent which exceed £2000 (HAVING) on each type of card. - Optional assignment: Write a query to produce this report: the first column lists all customers IDs, the second column shows customers last names, and the third column shows the total money each customer spent.
SELECT C.CustID, C.LastName, SUM(P.Price)
From Customer C, Payment P
WHERE C.CustID = P.CustID
GROUP BY C.CustID, C.LastName
HAVING SUM(Price);
This query selects customer IDs & Surnames from the Customer table & totals the amounts spent from the Payment table, where the customer ID from each table match; The records are grouped by Customer ID & Surname, with the HAVING clause used to identify the total amount spent by each customer. - Optional assignment: Write a query to count how many payments are made for each credit card type. The report should only show the list of credit card with more than 1 payment.
SELECT CreditCard
FROM Payment
GROUP BY CreditCard
HAVING COUNT(CreditCard) > 1;
This query select all credit cards where more than 2 payments have been made. - Optional assignment: Review another student's assignment. Pick the best query you have seen during this class.
Will do this part of the assignment tomorrow evening!
Week 4
- Review & revise your plan for 3 tables in the Week 1 assignment. Make sure that you will be able to write a join query for 3 tables.
- Write and post 3 "create table" queries. Give your tables meaningful names, include at least 5 fields, one required field, 3 data types for each table.
CREATE TABLE CycleRides
(RideId Autoincrement,
RideDate Date,
StartTime Date,
EndTime Date,
StartingAt Text(100),
Destination Text(100),
Distance Integer
);
CREATE TABLE Families
(FamilyId Autoincrement CONSTRAINT PK_Familes PRIMARY KEY,
Address Text(50),
City Text(35),
PostCode Text(10),
PhoneNo Text(15),
Email Text(30)
);
CREATE TABLE Rider
(RiderId Autoincrement CONSTRAINT PK_Rider PRIMARY KEY,
FirstName Text(35),
Surname Text(35),
Age Number,
AdultOrChild Bit,
FamilyID Integer,
Experience Text,
CycleProficiency Bit
);
CREATE TABLE RiderProfile
(RiderID autoincrement CONSTRAINT PK_RideParticipants PRIMARY KEY,
Adult Bit,
Child Bit,
Competent Bit,
SomeExperience Bit,
Novice Bit,
CycleProficiency Bit
);
CREATE TABLE RideParticipants
(ParticipantID Autoincrement CONSTRAINT PK_RideParticipants PRIMARY KEY,
RideID Integer,
RiderID Integer
);
- Write and post 5 "insert" queries to populate data for each table (15 queries total).
CycleRide INSERT INTO Queries:
INSERT INTO CycleRides
VALUES (1, #25/01/2009#, #10.30#, #14.30#, 'Royal Commonwealth Pool', 'Swanston Village',16);
INSERT INTO CycleRides
VALUES (2, #22/02/2009#, #10.30#, #13.30#, 'The Meadows', 'New Hailes House',10);
INSERT INTO CycleRides
VALUES (3, #29/03/2009#, #10:30#, #14:30#, 'Ainslie Park Leisure Centre', 'Cramond',12);
INSERT INTO CycleRides
VALUES (4, #26/04/2009#, #10:30#, #15:00#, 'Water of Leith Visitor Centre', 'Heriot-Watt University',15);
INSERT INTO CycleRides
VALUES (5, #31/05/2009#, #10:30#, #14:00#, 'Royal Commonwealth Pool', 'Dalkeith Country Park', 15);
Families INSERT INTO Queries:
INSERT INTOFamilies
VALUES (1, '5 St Marks Place', 'Edinburgh', 'EH15 2PY', '0131 669 6542', 'patsy@portypatsy.co.uk');
INSERT INTO Families
VALUES (2, '21 High St', 'Musselburgh', 'EH22 1XY', '0131 665 1234', 'fred@yahoo.co.uk');
INSERT INTO Families VALUES (3, '58 Gorgie Rd', 'Edinburgh', 'EH3 8JG', '0131 334 7645 ', 'maria.cull@hotmail.co.uk');
INSERT INTO Families
VALUES (4, '23 Dick Place', 'Edinburgh', 'EH4 2XY', '0131 667 9843 ', 'jeans75@aol.com');
INSERT INTO Families
VALUES (5, '78 High Riggs', 'Edinburgh', 'EH2 KL6', ' ', ' ', 72, ''); INSERT INTO Families
VALUES (6, '23 Dalkeith St', 'Edinburgh', 'EH15 8PO', '0131 669 7867', 'SallyS@yahoo.co.uk');
Rider INSERT INTO Queries:
INSERT INTO Rider
VALUES (1, ' Patsy', 'James', 57, 'Adult', 1, 'Competent', No);
INSERT INTO Rider
VALUES (2, ' Fred', 'Hughes', 42, 'Adult', 2, 'Competent', No);
INSERT INTO Rider
VALUES (3, 'Maria', 'Pepe', 45, 'Adult', 3, 'Novice', No);
INSERT INTO Rider
VALUES (4, ' Jean', 'Simpson', 67, 'Adult', 4, 'Novice', No);
INSERT INTO Rider
VALUES (5, ' Harry', 'Burns', 72, 'Adult', 5, 'Some Experience', No);
INSERT INTO Rider
VALUES (6, ' Sally', 'Smith', 36, 'Adult', 6, 'Some Experience', No);
INSERT INTO Rider
VALUES (7, 'Heather', 'Hughes', 11, 'Child' 2, 'Competent', Yes);
INSERT INTO Rider
VALUES (8, 'Ruth', 'Hughes', 8, 'Child' 2, 'Some Experience', No);
INSERT INTO Rider
VALUES (9, 'Atoli', 'Pepe', 9, 'Child' 3, 'Some Experience', No);
INSERT INTO Rider
VALUES (10, 'Andrew', 'James', 7, 'Child' 1, 'Competent', Yes);
INSERT INTO Rider
VALUES (11, 'Emma', 'Smith', 10, 'Child' 6, 'Competent', Yes);
RiderProfile INSERT INTO Queries:
INSERT INTO RiderProfile
VALUES (1, -1, 0, -1, 0, 0, 0);
INSERT INTO RiderProfile
VALUES (2, -1, 0, -1, 0, 0, 0);
INSERT INTO RiderProfile
VALUES (3, -1, 0, 0, 0, -1, 0);
INSERT INTO RiderProfile
VALUES (4, -1, 0, 0, 0, -1, 0);
INSERT INTO RiderProfile
VALUES (5, -1, 0, 0, -1, 0, 0);
INSERT INTO RiderProfile
VALUES (6, -1, 0, 0, -1, 0, 0);
INSERT INTO RiderProfile
VALUES (7, 0, -1, -1, 0, 0, -1);
INSERT INTO RiderProfile
VALUES (8, 0, -1, 0, -1, 0, 0);
INSERT INTO RiderProfile
VALUES (9, 0, -1, 0, -1, -1, 0);
INSERT INTO RiderProfile
VALUES (10, 0, -1, -1, 0, 0, 0);
INSERT INTO RiderProfile
VALUES 11, 0, -1, -1, 0, 0, -1);
RideParticipants INSERT INTO Queries:
INSERT INTO RideParticipants
VALUES (1, 1, 1);
INSERT INTO RideParticipants
VALUES (2, 1, 10);
INSERT INTO RideParticipants
VALUES (3, 1, 2);
INSERT INTO RideParticipants
VALUES (4, 1, 7);
INSERT INTO RideParticipants
VALUES (5, 1, 8);
INSERT INTO RideParticipants
VALUES (6, 1, 5);
INSERT INTO RideParticipants
VALUES (7, 1, 5);
INSERT INTO RideParticipants
VALUES (8, 2, 3);
INSERT INTO RideParticipants
VALUES (9, 2, -1);
INSERT INTO RideParticipants
VALUES (10, 2, -1);
INSERT INTO RideParticipants
VALUES 11, 2, -1);
- Post description for each table (e.g. Customers table will store the information about my customers and Payments table will store all the payments that my customers made).
The CycleRides table contains information about monthly rides for new & non-confident riders.
The Riders table contains information about all riders riders.
The Families table gives the contact details of each family. All children on the rides should be accompanied by an adult.
The RiderProfile table contains information about whether participants are adults / children & their level of experience, including whether children have passed their cycle profiency test.
The RideParticipants table contains information about which riders took part in each of the 5 rides.
- Write and post 3 "create table" queries. Give your tables meaningful names, include at least 5 fields, one required field, 3 data types for each table.
- Write a query to set up foreign key(s) for your tables.
ALTER TABLE Rider
ADD CONSTRAINT FK_Rider FOREIGN KEY (FamilyID) REFERENCES Families(FamilyId);
ALTER TABLE RideParticipants
ADD CONSTRAINT FK_RideParticipants FOREIGN KEY (RiderID) REFERENCES Rider(RiderId);
ALTER TABLE RideParticipants
ADD CONSTRAINT FK_RideParticipants FOREIGN KEY (RideID) REFERENCES CycleRides(RideId);
- Write a query to add a new field.
ALTER TABLE CycleRides
ADD COLUMN Distance Integer; - Write a query to delete one of the fields.
ALTER TABLE Rider
DROP COLUMN CycleProficiency; - Write a query to update your record(s).
UPDATE CycleRides
SET StartTime = #11.00#
WHERE RideDate = #31/05/2009#; - Write a query to delete a record.
DELETE FROM CycleRides
WHERE Distance > 16; - Write a query to create an index for one of your tables.
CREATE INDEX IDX_FamiliesEmail
ON Families (Email); - Write a query using default/inner JOIN.
SELECT R.RiderID, R.FirstName, R.Surname, RP.Competent
FROM Rider R, RiderProfile RP
WHERE R.RiderID = RP.RiderID
AND RP.Child = Yes
AND RP.Competent = Yes;
ORDER BY R.Surname ASC;
- Write a query using LEFT JOIN.
SELECT R.RiderID, R.FirstName, R.Surname, RP.Adult
FROM Rider R LEFT JOIN RiderProfile RP
ON R.RiderID = RP.RiderID
ORDER BY R.Surname ASC;
- Write a query using both COUNT & GROUP BY.
SELECT RideID, COUNT(*)
FROM RideParticipants
GROUP BY RideID;
- Write a query using inner JOIN for 3 tables.
SELECT CR.RideID, R.RiderID, CR.RideDate, CR.Destination, R.Experience, R.FirstName, R.Surname, F.PhoneNo
FROM Rider R, CycleRides CR, RideParticipants RP, Families F
WHERE CR.RideID = RP.RideID
AND RP.RiderID = R.RiderID
AND R.FamilyID = F.FamilyID
AND CR.RideID = 1
ORDER BY R.RiderID ASC;