Courses

Certificate in Web Application Development Net Resources IWA/HWG

Introduction to SQL using Access

Week 1

  1. SELECT DISTINCT Email
    FROM Customer;

  2. SELECT FirstName, LastName, State
    FROM Customer
    ORDER BY State ASC;

  3. SELECT Firstname
    FROM Customer
    ORDER BY FirstName DESC;
    Explanation: Whilst this query runs OK, it is good practice to capitalise field-names consistently.

  4. 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!).

  5. 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;

  6. SELECT FirstName, LastName, Address, City, State, Zip, Country
    FROM Customer
    ORDER BY Zip;

  7. 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.

    8. Table Name: CycleRide AdultCyclist ChildCyclist
    Fields: RideID (PK)
    RideDate
    Duration
    StartTime
    EndTime
    StartingAt
    Destination
    AdultID (FK)
    		 AdultID (PK)
    FirstName
    LastName
    Address1
    Address2
    Town/City
    PostCode
    AgeRange
    		 ChildID (PK)
    FirstName
    LastName
    Age
    AdultID (FK)
    Key: (PK) = Primary Key       (FK) = Foreign Key

  8. Table Name: InsurancePolicy Customer Dependent
    Fields: EffectiveDate
    Duration
    CustomerID (FK)
    PolicyID (PK)
    Status
    		 FirstName
    LastName
    Address
    DoB
    CustomerID (PK)
    		 FirstName
    LastName
    DependentID (PK)
    DoB
    InsuredID (FK)
    Key: (PK) = Primary Key       (FK) = Foreign Key

^Top^

Week 2

  1. Show first names & last names of customers living in California

    SELECT FirstName, LastName
    FROM Customer
    WHERE State = 'CA';

  2. 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;

  3. Show customers whose first names start with letter M

    SELECT *
    FROM Customer
    WHERE FirstName LIKE 'M*';

  4. Show customers living in the USA & don't have the phone number

    SELECT *
    FROM Customer
    WHERE Country = 'USA' AND Phone IS NULL;

  5. Show customers living in California & customers living in Texas

    SELECT *
    FROM Customer
    WHERE State = 'CA' OR State = 'TX';

  6. Show payments paid by Visa card and the price is higher than $100

    SELECT *
    FROM Payment
    WHERE CreditCard = 'VISA' AND Price > 100;

  7. Show customer ID of those who bought Sony CPU

    SELECT CustID
    FROM Payment
    WHERE Description = 'Sony CPU';

  8. 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#;

  9. 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.

  10. 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

  1. 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.

  2. 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.

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


  4. Write a query for the Sales table using [ ] or [! ] and explain the query.

^Top^

Week 3

  1. 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;

  2. 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#;

  3. Find out how many payments that are more than $50

    SELECT COUNT(PaymentID)
    FROM Payment
    WHERE Price > 50;

  4. Find out the average payments paid in 2005

    SELECT AVG(Price)
    FROM Payment
    WHERE PaidDate >= #01/01/2005# AND PaidDate <= #31/12/2005#;

  5. Find out the highest payment in 2005

    SELECT MAX(Price)
    FROM Payment
    WHERE PaidDate >= #01/01/2005# AND PaidDate <= #31/12/2005#;

  6. 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

  7. 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.

  8. 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.

  9. 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.

  10. 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!

^Top^

Week 4

  1. 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.

    1. 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
      );


    2. 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);


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


  2. 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);

  3. Write a query to add a new field.

    ALTER TABLE CycleRides
    ADD COLUMN Distance Integer;

  4. Write a query to delete one of the fields.

    ALTER TABLE Rider
    DROP COLUMN CycleProficiency;

  5. Write a query to update your record(s).

    UPDATE CycleRides
    SET StartTime = #11.00#
    WHERE RideDate = #31/05/2009#;

  6. Write a query to delete a record.

    DELETE FROM CycleRides
    WHERE Distance > 16;

  7. Write a query to create an index for one of your tables.

    CREATE INDEX IDX_FamiliesEmail
    ON Families (Email);

  8. Write a query using default/inner JOIN.
    Inner Join Query
    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;


  9. Write a query using LEFT JOIN.
    Left Join Query
    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;




  10. Write a query using both COUNT & GROUP BY.
    Count/Group By Query
    SELECT RideID, COUNT(*)
    FROM RideParticipants
    GROUP BY RideID;


  11. 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;
    Inner Join 4 Tables Query

^Top^

Valid CSS! Valid XHTML 1.0 Transitional Web design: Patsy James