Activity: SQL wrap-up

(no submission)

Purpose:

You may make a copy of a worksheet and complete this activity, or simply type your answers in any text editor.

You may work alone or with at most 2 other students (max size=3) in this course.

Answer the following questions / complete the following tasks.

  1. Which of the following are constraints that can be automatically checked by a DBMS when tuples are changed or added to the table? Circle all that apply. 
    (a) value in column is a string
(b) value in column is not null
(c) value in column is unique in that column of the table
(d) value in column is the key of a tuple in the table 
(e) value in column is a positive integer
(f) values in columns are different
(g) date values in column is not in the past
(h) all of the above
(i) none of the above
  2. An online picture sharing company uses a database with the following schema: 
    Users(uid, uname, city)
Picture(pid, author, size, pdf)

Users stores all users; uid is the key.

Picture stores their pictures; pid is the key; 
author is the uid of the picture's author; 
size represents the size of the picture in bytes; 
pdf is the actual pdf content of the picture.

uid, pid, author, size are integers; uname, city, pdf are text.
    1. Write the SQL statements to create the tables for this database.
    2. Write the SQL statement(s) to add a check constraint to the Picture table to ensure that the size of the picture is limited to 3MB (3000000 bytes).
    3. Come up with some data, including pictures with size < 3MB and pictures with size > 3MB. Write the SQL statement(s) to insert some data into the tables for this database.
    4. Write a SQL query that returns all users that have posted both a picture larger than 1MB (size > 1000000) and a picture smaller than 1MB. Your query should return the users' uid and uname.
    5. Write a SQL query that retrieves all users who do not have any picture greater than 1MB (size > 1000000). Your query should return the users' uid and uname.
  3. [optional] Come up with at least one trigger that is appropriate to this online picture sharing database. Then write the SQL statement(s) to create the trigger.
  4. [optional] Use the following database schema. Find the names of the sailors who have reserved all the boats. 
    Boats (bid, bname, color)
Sailors (sid, sname, rating, age)
Reserves (sid, bid, day)

    To test your SQL, you should insert some data. The following SQL commands are provided for your convenience. 

    INSERT INTO Reserves VALUES (22, 102, '2023-03-13');
INSERT INTO Reserves VALUES (22, 103, '2023-03-13');
INSERT INTO Reserves VALUES (22, 105, '2023-03-13');
Copyright © 2024 Upsorn Praphamontripong

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2024-02-27 20:49