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
    all 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. 
      create table Users ( uid int,
                     uname text not null,
                     city text not null,
                     primary key(uid) );
                     
create table Picture ( pid int,
                     author int not null references Users(uid),
                     size int not null,
                     pdf text,
                     primary key(pid) );

Alternatively, for Picture, we can specify 
                     foreign key (author) references Users(uid)
    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). 
      ALTER TABLE Picture
ADD CONSTRAINT checkSize
CHECK (size <= 3000000);
    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. 
      insert into Users values(1, 'Minnie', 'Charlottesville');
insert into Users values(2, 'Mickey', 'Charlottesville');
insert into Users values(3, 'Goofy', 'Richmond');
insert into Users values(4, 'Daisy', 'Charlottesville');
insert into Users values(5, 'Donald', 'Fairfax');

insert into Picture values(10, 1, 1500000, 'some pdf abc content');
insert into Picture values(20, 1, 1500000, 'some pdf def content');
insert into Picture values(30, 2, 1500000, 'some pdf ghi content');
insert into Picture values(40, 1, 1500000, 'some pdf jkl content');
insert into Picture values(50, 2, 1500000, 'some pdf mno content');
insert into Picture values(60, 5,  500000, 'some pdf pqr content');
insert into Picture values(70, 5, 3000000, 'some pdf stu content');
insert into Picture values(80, 5, 4000000, 'some pdf vwx content');  -- this should not be added due to checkSize

Note: the order of insertion -- insert into the Users table before the Product table
    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. 
      select U.uid, U.uname
from   Users U, Picture P1, Picture P2
where  U.uid = P1.author and U.uid = P2.author
  and  P1.size > 1000000 and P2.size <= 1000000;
    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. 
      select U.uid, U.uname
from   Users U
where  not exists (select *
                   from   Picture P
                   where  P.size > 1000000 and U.uid = P.author);

-- OR -- 

select U.uid, U.uname
from   Users U
where  U.uid not IN (select P.author
                   from   Picture P
                   where  P.size > 1000000);

-- OR --

select U.uid, U.uname
from   Users U
where  1000000 > ALL (select P.size
                   from   Picture P
                   where  P.author = U.uid);
  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'); 
    For each sailer, there is no boats that this sailor has not reserved
(note: double negation)

SELECT  sname 
FROM    Sailors 
WHERE NOT EXISTS 
        (SELECT bid 
        FROM    Boats 
        WHERE NOT EXISTS 
                (SELECT Reserves.bid 
                FROM    Reserves
                WHERE   Reserves.bid = Boats.bid 
                    AND Reserves.sid = Sailors.sid) );

Copyright © 2024 Upsorn Praphamontripong

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

Last updated 2024-02-27 20:49