SQL Queries Assignment - Database Module, University Name

This document presents a comprehensive solution to an SQL queries assignment, focusing on the use of MySQL Workbench. The assignment is divided into two main parts: Part A covers Data Manipulation Language (DML) queries, including SELECT statements to retrieve and filter data from tables such as 'vendor', 'Customer', 'Invoice', and 'Line'. These queries involve joins, distinct selections, and the use of aggregate functions to analyze customer and invoice data. Part B addresses Data Definition Language (DDL) queries, demonstrating how to modify database structures. This includes INSERT statements to add new records, ALTER TABLE statements to add new columns, UPDATE statements to modify existing data, and DELETE statements to remove records. The assignment also includes a bibliography citing relevant sources on database design and MySQL Workbench usage. The solution showcases practical application of SQL commands for database management, providing a valuable resource for students learning database concepts and SQL syntax.

Running head: SQL QUERIES USING WORKBENCH
SQL Queries Using Workbench
Name of the Student
Name of the University

1SQL QUERIES USING WORKBENCH
Part A - DML
Query 1:
SELECT * FROM vendor WHERE v_state = '';
Query 2:
select * from Customer where CUS_BALANCE>300 AND CUS_BALANCE<400;

2SQL QUERIES USING WORKBENCH
Query 3:
SELECT DISTINCT CONCAT(CUS_FNAME, ' ', CUS_LNAME) AS 'Customer Name'
FROM Customer cus INNER JOIN Invoice inv ON cus.CUS_CODE = inv.CUS_CODE;

3SQL QUERIES USING WORKBENCH
Query 4:
SELECT DISTINCT CONCAT(CUS_FNAME, ' ', CUS_LNAME) AS 'Customer Name'
FROM Customer cus INNER JOIN Invoice inv ON cus.CUS_CODE = inv.CUS_CODE;

4SQL QUERIES USING WORKBENCH
Query 5:
SELECT DISTINCT CUS_CODE FROM Customer cus WHERE NOT EXISTS
(SELECT * FROM Invoice inv WHERE cus.CUS_CODE = inv.CUS_CODE);

5SQL QUERIES USING WORKBENCH
Query 6:
Select distinct Concat(cus .CUS_FNAME, ' ', cus .CUS_LNAME) As 'Customer Name'
from customer cus where NOT EXISTS (select * from Invoice inv where cus.CUS_CODE =
inv.CUS_CODE);

6SQL QUERIES USING WORKBENCH
Query 7:
SELECT cus.CUS_CODE, count(li.INV_NUMBER) AS 'Invoices_generated' FROM
Customer cus INNER JOIN invoice inv ON cus.CUS_CODE = inv.CUS_CODE INNER JOIN
Line li ON inv.INV_NUMBER=li.INV_NUMBER GROUP BY cus.CUS_CODE;

7SQL QUERIES USING WORKBENCH
Query 8:
SELECT cus.CUS_CODE, CONCAT(cus.CUS_FNAME, ' ', cus. CUS_LNAME) As
'Customer Name', count(li.INV_NUMBER) as ' N_Of_Invoices ' FROM Customer cus INNER
JOIN INVOICE inv ON cus.CUS_CODE = inv.CUS_CODE INNER JOIN Line li ON
inv.INV_NUMBER=li.INV_NUMBER GROUP BY cus.CUS_CODE;
Part B – DDL
Query 1:
INSERT INTO CUSTOMER VALUES('11649523', 'Palla', 'Yashwanth', 'Y', '615', '844-
2573', ' 234.0');

8SQL QUERIES USING WORKBENCH
Query 2:
ALTER TABLE CUSTOMER ADD credit_history VARCHAR(6);

9SQL QUERIES USING WORKBENCH
Query 3:
UPDATE assignment5.CUSTOMER SET credit_history = 'BAD' WHERE CUS_CODE
= 10011;

10SQL QUERIES USING WORKBENCH
Query 4:
Delete FROM assignment5.EMP WHERE EMP_LNAME = 'Genkazi' AND
EMP_FNAME = 'Leighla';

11SQL QUERIES USING WORKBENCH
Query 5:
ALTER TABLE EMP ADD email_address VARCHAR(20);

12SQL QUERIES USING WORKBENCH

13SQL QUERIES USING WORKBENCH
Bibliography:
Letkowski, J. (2015). Doing database design with MySQL. Journal of Technology Research, 6,
1.
Yang, L., & Cao, L. (2016). The Effect of MySQL Workbench in Teaching Entity-Relationship
Diagram (ERD) to Relational Schema Mapping. International Journal of Modern
Education and Computer Science, 8(7), 1.