SQLite Assignment Help | Database Assignment Help


In this blog we will covers all query related to SQLite database, Below the details of database tables:


  1. albums are the albums our store is selling

  2. artists are all the artists

  3. tracks are all the tracks, containing a column that tells us which album the track belongs to.

  4. media_types tell us what format a track is in, e.g., MP3, Apple Audio, etc.

  5. genres are a list of genres.

  6. playlists are, well, playlists. A play list has many tracks and a single track can belong to many playlists.

  7. playlist_tracks tells us which tracks belong to which playlists

  8. customers are our customer records and they may or may not have an assigned "customer support representative"

  9. invoices are a list of specific customer orders

  10. invoice_lines are the line-items on specific invoices

  11. employees contains information about our company's employees. This is mostly used to assign support representatives to customers.


Creating Tables:

CREATE TABLE playlist_tracks

(

playlist_id INTEGER NOT NULL,

track_id INTEGER NOT NULL,

CONSTRAINT PK_PlaylistTrack PRIMARY KEY (playlist_id, track_id),

FOREIGN KEY (playlist_id) REFERENCES playlists (id)

ON DELETE NO ACTION ON UPDATE NO ACTION,

FOREIGN KEY (track_id) REFERENCES tracks (id)

ON DELETE NO ACTION ON UPDATE NO ACTION

)


CREATE TABLE playlists

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

name VARCHAR(120)

)


CREATE TABLE invoice_lines

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

invoice_id INTEGER NOT NULL,

track_id INTEGER NOT NULL,

unit_price NUMERIC(10,2) NOT NULL,

quantity INTEGER NOT NULL,

FOREIGN KEY (invoice_id) REFERENCES invoices (id)

ON DELETE NO ACTION ON UPDATE NO ACTION,

FOREIGN KEY (track_id) REFERENCES tracks (id)

ON DELETE NO ACTION ON UPDATE NO ACTION

)


CREATE TABLE tracks

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

name VARCHAR(200) NOT NULL,

album_id INTEGER,

media_type_id INTEGER NOT NULL,

genre_id INTEGER,

composer VARCHAR(220),

milliseconds INTEGER NOT NULL,

bytes INTEGER,

unit_price NUMERIC(10,2) NOT NULL,

FOREIGN KEY (album_id) REFERENCES albums (id)

ON DELETE NO ACTION ON UPDATE NO ACTION,

FOREIGN KEY (genre_id) REFERENCES genres (id)

ON DELETE NO ACTION ON UPDATE NO ACTION,

FOREIGN KEY (media_type_id) REFERENCES media_types (id)

ON DELETE NO ACTION ON UPDATE NO ACTION

)


CREATE TABLE media_types

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

name VARCHAR(120)

)


CREATE TABLE invoices

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

customer_id INTEGER NOT NULL,

invoice_date TIMESTAMP NOT NULL,

billing_address VARCHAR(70),

billing_city VARCHAR(40),

billing_state VARCHAR(40),

billing_country VARCHAR(40),

billing_postal_code VARCHAR(10),

total NUMERIC(10,2) NOT NULL,

FOREIGN KEY (customer_id) REFERENCES customers (id)

ON DELETE NO ACTION ON UPDATE NO ACTION

)


CREATE TABLE genres

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

name VARCHAR(120)

)


CREATE TABLE customers

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name VARCHAR(40) NOT NULL,

last_name VARCHAR(20) NOT NULL,

company VARCHAR(80),

address VARCHAR(70),

city VARCHAR(40),

state VARCHAR(40),

country VARCHAR(40),

postal_code VARCHAR(10),

phone VARCHAR(24),

fax VARCHAR(24),

email VARCHAR(60) NOT NULL,

support_rep_id INTEGER,

FOREIGN KEY (support_rep_id) REFERENCES employees (id)

ON DELETE NO ACTION ON UPDATE NO ACTION

)


CREATE TABLE employees

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

last_name VARCHAR(20) NOT NULL,

first_name VARCHAR(20) NOT NULL,

title VARCHAR(30),

reports_to INTEGER,

birth_date TIMESTAMP,

hire_date TIMESTAMP,

address VARCHAR(70),

city VARCHAR(40),

state VARCHAR(40),

country VARCHAR(40),

postal_code VARCHAR(10),

phone VARCHAR(24),

fax VARCHAR(24),

email VARCHAR(60),

FOREIGN KEY (reports_to) REFERENCES employees (id)

ON DELETE NO ACTION ON UPDATE NO ACTION

)


CREATE TABLE albums

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

title VARCHAR(160) NOT NULL,

artist_id INTEGER NOT NULL,

FOREIGN KEY (artist_id) REFERENCES artists (id)

ON DELETE NO ACTION ON UPDATE NO ACTION

)


CREATE TABLE artists

(

id INTEGER PRIMARY KEY AUTOINCREMENT,

name VARCHAR(120)

)


Working With Queries:


-- Every album

SELECT*FROM albums;


-- Every album title (and only the title)

SELECT title FROM albums;


-- Every album ordered by album title in ascending order (A-Z)-- Note: ORDER BY orders by lowest-to-highest by default

SELECT*FROM albums ORDER BY title;


-- Every album ordered by album title in descending order (Z-A)

SELECT*FROM albums ORDER BY title DESC;


-- Every album whose title starts with A in alphabetical order

SELECT*FROM albums WHERE title LIKE'A%'ORDER BY title;


-- "invoices" table-- Remember: run ".schema invoices" to see the structure of the "invoices" table-- Every invoice-- Every invoice ordered by total invoice amount ("total")-- Every invoice with a total greater than 10-- The 10 least expensive invoices-- Remember: ORDER BY orders from lowest-to-highest by default

SELECT*FROM invoices ORDER BY total LIMIT10;


-- The 10 most expensive invoices-- The 15 most recent invoices-- The 15 oldest invoices-- The 10 most expensive invoices from the US-- The 10 least expensive invoices from the US-- The 10 most expensive invoices from outside the US-- Hint: If "=" means equal, use "!=" to mean "not equal"-- Every invoice from Chicago, IL

SELECT*FROM invoices WHERE billing_city ="Chicago"AND billing_state ="IL"AND billing_country="USA";


-- A list of all the invoices worth more than $5.00 from Chicago, IL-- The billing addresses of the 5 most valuable invoices from Mountain View CA-- Gotta reward those big spenders!-- A list of the 10 most valuable invoices made before January 1st, 2010-- Hint: Dates are formatted like 'YYYY-MM-DD' and you can compare them using '<', '>', '<=' and '>='-- The number of invoices from Chicago, IL

SELECTCOUNT(*) FROM invoices WHERE billing_city ="Chicago"AND billing_state ="IL"AND billing_country ="USA";


-- The number of invoices from the US, grouped by state

SELECT billing_state, COUNT(*) FROM invoices WHERE billing_country ="USA"GROUP BY billing_state;


-- The state in the US with the most invoices

SELECT billing_state, COUNT(*) FROM invoices WHERE billing_country ="USA"GROUP BY billing_state ORDER BYCOUNT(*) DESCLIMIT1;


-- The total invoice value from California

SELECT billing_state, SUM(total) FROM invoices WHERE billing_country ="USA"and billing_state ="CA";


-- The number of invoices and the invoice total from California

SELECT billing_state, COUNT(*), SUM(total) FROM invoices WHERE billing_country ="USA"and billing_state ="CA";


-- The count, total, and average of invoice totals from California

SELECT billing_state, COUNT(*), SUM(total), AVG(total) FROM invoices WHERE billing_country ="USA"and billing_state ="CA";


-- The count, total, and average of invoice totals, grouped by state, ordered by average invoice total from highest-to-lowest


-- A list of the top 5 countries by number of invoices


-- A list of the top 5 countries by gross/total invoice size-- A list of the top 5 countries by average invoice size



Get Solution of above queries or need any database project assignment help then you can contact us at realcode4you@gmail.com


10 views0 comments