SQL Queries Using Workbench

This assignment focuses on demonstrating various SQL queries using MySQL Workbench. It includes both Data Manipulation Language (DML) queries for tasks like selecting, inserting, updating, and deleting data from tables like 'Customer' and 'Vendor', as well as Data Definition Language (DDL) queries for creating, modifying, and altering table structures. The assignment provides a step-by-step guide on executing these queries and achieving specific results.

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.