SQL Queries Assignment Solution - Database Systems Coursework

This assignment solution provides a detailed breakdown of SQL queries using SQLite. The solution includes queries for tasks such as selecting data, filtering based on conditions, joining tables, and performing aggregate functions. The queries cover a range of database operations, including selecting, inserting, updating, and deleting data. The solution also includes the creation of new database tables and the modification of existing ones. The document includes code examples and explanations for each query, making it a valuable resource for students learning SQL and database management. The assignment covers topics such as data retrieval, data manipulation, and database schema design. Furthermore, a bibliography is provided for reference.

Running head: SQL QUERY USING SQLITE
SQL Query Using SQLite
Name of the Student
Student ID
Subject Code

1SQL QUERY USING SQLITE
Part A:
Query 1:
select * from vendor where v_state = '';
Query 2:
select * from customer where CUS_BALANCE>300 AND CUS_BALANCE<400;
Query 3:
Select DISTINCT CUS_LNAME from customer inner join invoice on
customer.CUS_CODE = invoice.CUS_CODE;

2SQL QUERY USING SQLITE
Query 4:
select max (CUS_BALANCE) from customer;
Query 5:
Select distinct CUS_CODE from customer where NOT EXISTS (select * from invoice
where customer.CUS_CODE = invoice.CUS_CODE);
Query 6:
Select distinct (CUS_FNAME || ' ' || CUS_LNAME) As 'Customer Name' from customer
where NOT EXISTS (select * from invoice where customer.CUS_CODE =
invoice.CUS_CODE);

3SQL QUERY USING SQLITE
Query 7:
select c.CUS_CODE, count(l.INV_NUMBER) as 'Invoices_generated' from Customer c
inner join invoice i on c.CUS_CODE = i.CUS_CODE inner join line l ON
i.INV_NUMBER=l.INV_NUMBER Group BY c.CUS_CODE;
Query 8:
select c.CUS_CODE, (CUS_FNAME || ' ' || CUS_LNAME) As 'Customer Name',
count(l.INV_NUMBER) as 'Invoices_generated' from Customer c inner join invoice i on
c.CUS_CODE = i.CUS_CODE inner join line l ON i.INV_NUMBER=l.INV_NUMBER Group
BY c.CUS_CODE;
Part B:
Query 1:
INSERT INTO CUSTOMER VALUES(' 11537446 ',' VAISHNAV','
VIPULKUMAR','A','615','844-2573','0');

4SQL QUERY USING SQLITE
Query 2:
alter table customer add credit_history VARCHAR(6);
Query 3:
update customer set credit_history = 'BAD' where CUS_CODE = 10011;
Query 4:
delete from EMP where EMP_LNAME = 'Genkazi' AND EMP_FNAME = 'Leighla';

5SQL QUERY USING SQLITE
Query 5:
Alter table emp add email_address VARCHAR(20);

6SQL QUERY USING SQLITE
Bibliography:
Kumar, R., Gupta, N., Charu, S., Jain, K., & Jangir, S. K. (2014). Open source solution for cloud
computing platform using OpenStack. International Journal of Computer Science and
Mobile Computing, 3(5), 89-98.
Ripley, B., Lapsley, M., & Ripley, M. B. (2017). Package ‘RODBC’.