TASK
back to top
In this assignment, you are supplied a simple dataset on which you will write SQL queries. The dataset is provided in SQLite format. Your are also allowed to complete this assessment using MySQL or MariaDB, but it will be your responsibility to convert the supplied dataset into a format compatible with your DMBS.
The dataset file ‘Ass4.txt’ can be downloaded from ‘Assessment 4’ page in Interact site left hand menu.
Instructions for SQLite
For guidance on how to install SQLite, watch this short video: https://www.youtube.com/watch?v=9ynCDVg54H0
For guidance on how to create tables using SQLite, watch this short video: https://www.youtube.com/watch?v=kij5lPw0F-o
- Create a new folder on your machine.
- Copy sqlite3.exe (downloaded from sqlite.org) and dataset file Ass4.txt to this new folder.
- Run sqlite3.exe to launch its command prompt.
- Execute the Ass4.txt script on the prompt. To do this, type .read Ass4.txt [It will create the tables you will use to answer this assignment’s questions]
- At the SQLite prompt, type .headers on
- At the SQLite prompt, type .mode column
Task
Write and run SQL statements to complete the following tasks (each task is worth 2 marks, 1 mark for query & 1 mark for result)
1. Show session code, subject name, and lecturer name for the 202060 session using WHERE conditions only – i.e. No joins.
2. Show session code, subject name, and lecturer name using ‘JOIN ON’.
3. Show session code, subject name, and lecturer name using ‘JOIN USING’.
4. Show session code, subject name, and lecturer name using ‘NATURAL JOIN’.
5. List the names of the lectures and the number of sessions each lecturer has taught or will teach.
6. Add a record for Josh whose student ID is 4444.
7. Having added a record for Josh, show the names of students who did not join any class yet.
8. Which school has the minimum number of lecturers?
The post Instructions for SQLite appeared first on My Assignment Online.