You are to complete a collection of exercises on database views.
A set of commands to create a set of tables in a database and to insert a collection of records are given. You will first execute these commands to create the tables. You will then write the commands to create and use the views, following the discussion in the lecture notes or its PowerPoint version.
This set of commands creates a collection of tables, including Sneakers, Buyers, Stores, Sells, Likes, BuysAt. The next set of commands inserts a collection of records into these tables. You are free to include more tables or records, if you'd like.
After executing the above commands, you will have created a database that contains these information. Let's name this database view.db. Your work now is write commands to create and use views from this database. Refer to the lecture notes for specifics.
- Create a view named CanBuy which lists the store names and the sneaker brands that these stores are selling.
- Print out the content of the CanBuy view.
- Print out all the sneaker brands that the store named 'Danville' sells.
- Create a view named Synergy which lists buyers, sneakers, and stores from the tables of Likes, Sells, BuysAt that the buyers actually buys the sneakers she likes at a particular store.
- Print out the content of the Synergy view.
- Discuss with your partner and create one more view of interest and run some queries against it.
Submission
Submit your commands in a text file named Activity7-views.sql that creates and uses the views. In addition, submit a screen shot of executing these commands in a text file named Actvitiy7-views.txt, either through the Linux utitlity script, or using copy-and-paste.
References
- SQLite Tutorial on Views by SQLITE TUTORIAL. Accessed 2018/03/21.