<MySQL DML practice 1 | Main Java & Gang |MySQL DML practice 2 Part 2 >

MySQL Database and NetBeans Practice:

Another MySQL DML Exercise 2 Part 1

Contents:

• MySQL Data Definition Language (DDL)

• DDL Exercises

• The MySQL Data Manipulation Language (DML)

1. Testing Rows Using the WHERE Clause

2. Show all Columns

3. Inexact Matching with the LIKE Operator

4. Condition on Date Columns

5. Complex Logical Expression

6. Join Tables but Show Column from One Table Only

7. Join Tables and Show Columns from Both Tables

8. Join Tables Using a Join Operation in the FROM Clause

9. Grouping on a Single Column

10. Counting Rows and Unique Column Values

11. Grouping with Row Conditions

12. Grouping with Row and Group Conditions

13. Grouping all Rows

14. Grouping on Two Columns

15. Combining Grouping and Joins

16. Sorting on a Single Column

17. Sorting on Two Columns with Descending Order

18. Result with Duplicates

19. Eliminating Duplicates with DISTINCT

20. Depict Many Parts of SELECT Statement

21. Joining Two Tables

22. Join with Duplicates

23. Join with Duplicates Removed

24. Joining Three Tables with Columns from Only Two Tables

25. Joining Three Tables with Columns from Only Two Tables

26. Joining Four Tables

27. Joining Five Tables

28. Joining Two Tables without Matching on a Primary and Foreign Key

29. Join Two Tables Using the Join Operator Style

30. Join Three Tables Using the Join Operator Style

31. Join Four Tables Using the Join Operator Style

32. Combine the Cross Product and Join Operator Styles

33. Self Join

34. More Than One Join between Tables Using Alias Table Names

35. Join with Grouping on Multiple Columns

36. Join, Grouping and Group Conditions

37. UNION Query

• Data Control Language (DCL)

2.     Fill in the root’s password and click OK.

3.     The following Figure shows that the connection was established.

4.     The following Figure shows that the connection to the tid5013practice database has been established.

DDL Exercises

1.     Let execute SQL queries. Select thetid5013practice connection > right click mouse > select Execute Command. This will launch query editor on the right window.

2.     Copy and paste the following SQL script to create a table named student and then insert sample data. You may need to clean up the HTML format if you directly copy and paste to the SQL query editor.  Paste into Wordpad, clean-up the format, re-copy and re-paste into SQL query editor or get the SQL script here.

-- verify that the table is not exist yet

-- else we drop and recreate later

DROP TABLE IF EXISTS Student;

-- create a table named student

CREATE TABLE Student(

stdSSN        char(11),

stdFirstName  varchar(30) not null,

stdLastName        varchar(30) not null,

stdCity       varchar(30) not null,

stdState       char(2) not null,

stdZip        char(10) not null,

stdMajor       char(6),

stdClass       char(2),

stdGPA        decimal(3,2),

CONSTRAINT StudentPk PRIMARY KEY (StdSSN)

) type=innodb;

-- insert sample data

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('123-45-6789','HOMER','WELLS','SEATTLE','WA','IS','FR',3.00,'98121-1111');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('124-56-7890','BOB','NORBERT','BOTHELL','WA','FIN','JR',2.70,'98011-2121');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('234-56-7890','CANDY','KENDALL','TACOMA','WA','ACCT','JR',3.50,'99042-3321');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('345-67-8901','WALLY','KENDALL','SEATTLE','WA','IS','SR',2.80,'98123-1141');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('456-78-9012','JOE','ESTRADA','SEATTLE','WA','FIN','SR',3.20,'98121-2333');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('567-89-0123','MARIAH','DODGE','SEATTLE','WA','IS','JR',3.60,'98114-0021');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('678-90-1234','TESS','DODGE','REDMOND','WA','ACCT','SO',3.30,'98116-2344');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('789-01-2345','ROBERTO','MORALES','SEATTLE','WA','FIN','JR',2.50,'98121-2212');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('876-54-3210','CRISTOPHER','COLAN','SEATTLE','WA','IS','SR',4.00,'98114-1332');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('890-12-3456','LUKE','BRAZZI','SEATTLE','WA','IS','SR',2.20,'98116-0021');

INSERT INTO student(stdSSN, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip)

     VALUES ('901-23-4567','WILLIAM','PILGRIM','BOTHELL','WA','IS','SO',3.80,'98113-1885');

3.     Repeat the previous steps for other tables and sample data.

DROP TABLE IF EXISTS Course;

create table Course(

CourseNo    char(6) not null,

crsDesc     varchar(50) not null,

CrsUnits    integer,

CONSTRAINT CoursePK PRIMARY KEY (CourseNo)

)type=innodb;

INSERT INTO course(CourseNo, crsDesc, CrsUnits)

  VALUES ( 'FIN300','FUNDAMENTALS OF FINANCE',4);

INSERT INTO course(CourseNo, crsDesc, CrsUnits)

  VALUES ( 'FIN450','PRINCIPLES OF INVESTMENTS',4);

INSERT INTO course(CourseNo, crsDesc, CrsUnits)

  VALUES ( 'FIN480','CORPORATE FINANCE',4); 

INSERT INTO course(CourseNo, crsDesc, CrsUnits)

  VALUES ('IS320','FUNDAMENTALS OF BUSINESS PROGRAMMING',4 );

INSERT INTO course(CourseNo, crsDesc, CrsUnits)

  VALUES ( 'IS460','SYSTEMS ANALYSIS',4);

INSERT INTO course(CourseNo, crsDesc, CrsUnits)

  VALUES ( 'IS470','BUSINESS DATA COMMUNICATIONS',4);

INSERT INTO course(CourseNo, crsDesc, CrsUnits)

  VALUES ('IS480','FUNDAMENTALS OF DATABASE MANAGEMENT',4 );

DROP TABLE IF EXISTS Faculty;

CREATE TABLE Faculty(

FacSSN        char(11) not null,

FacFirstName  varchar(30) not null,

FacLastName   varchar(30) not null,

FacCity       varchar(30) not null,

FacState      char(2) not null,

FacZipCode    char(10) not null,

FacRank       char(4),

FacHireDate   date,

FacSalary     decimal(10,2),

FacSupervisor char(11),

FacDept       char(6),

CONSTRAINT FacultyPK PRIMARY KEY (FacSSN)

/*CONSTRAINT SupervisorFK FOREIGN KEY (FacSupervisor) REFERENCES Faculty ON DELETE SET NULL ON UPDATE CASCADE*/

)type=innodb;

INSERT INTO faculty

     (FacSSN, FacFirstName, FacLastName, FacCity, FacState,FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)

      VALUES ('543-21-0987','VICTORIA','EMMANUEL','BOTHELL','WA','MS','PROF',120000.0,'','1996-04-15','98011-2242');

INSERT INTO faculty

     (FacSSN, FacFirstName, FacLastName, FacCity, FacState,FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)

      VALUES ('765-43-2109','NICKI','MACON','BELLEVUE','WA','FIN','PROF',65000.00,'','1997-04-11','98015-9945');

INSERT INTO faculty

     (FacSSN, FacFirstName, FacLastName, FacCity, FacState,FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)

      VALUES ('654-32-1098','LEONARD','FIBON','SEATTLE','WA','MS','ASSC',70000.00,'543-21-0987','1994-05-01','98121-0094');

INSERT INTO faculty

     (FacSSN, FacFirstName, FacLastName, FacCity, FacState,FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)

      VALUES ('098-76-5432','LEONARD','VINCE','SEATTLE','WA','MS','ASST',35000.00,'654-32-1098','1995-04-10','98111-9921');

INSERT INTO faculty

     (FacSSN, FacFirstName, FacLastName, FacCity, FacState,FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)

      VALUES ('876-54-3210','CRISTOPHER','COLAN','SEATTLE','WA','MS','ASST',40000.00,'654-32-1098','1999-03-01','98114-1332');

INSERT INTO faculty

     (FacSSN, FacFirstName, FacLastName, FacCity, FacState,FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)

      VALUES ('987-65-4321','JULIA','MILLS','SEATTLE','WA','FIN','ASSC',75000.00,'765-43-2109','2000-03-15','98114-9954');

DROP TABLE IF EXISTS Offering;

create table Offering(

OfferNo       INTEGER not null,

CourseNo      char(6) not null,

OffTerm       char(6) not null,

OffYear       INTEGER not null,

OffLocation   varchar(30),

OffTime       varchar(10),

FacSSN        char(11),

OffDays       char(4),

CONSTRAINT OfferingPK PRIMARY KEY (OfferNo)

/*CONSTRAINT CourseFK FOREIGN KEY (CourseNo) REFERENCES Course

CONSTRAINT FacultyFK FOREIGN KEY (FacSSN) REFERENCES Faculty*/

)type=innodb;

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(1111,'IS320','SUMMER',2006,'BLM302','10:30:00','','MW');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(1234,'IS320','FALL',2005,'BLM302','10:30:00','098-76-5432','MW');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(2222,'IS460','SUMMER',2005,'BLM412','13:30:00','','TTH');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(3333,'IS320','SPRING',2006,'BLM214','08:30:00','098-76-5432','MW');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(4321,'IS320','FALL',2005,'BLM214','15:30:00','098-76-5432','TTH');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(4444,'IS320','WINTER',2006,'BLM302','15:30:00','543-21-0987','TTH');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(5555,'FIN300','WINTER',2006,'BLM207','08:30:00','765-43-2109','MW');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(5678,'IS480','WINTER',2006,'BLM302','10:30:00','987-65-4321','MW');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(5679,'IS480','SPRING',2006,'BLM412','15:30:00','876-54-3210','TTH');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(6666,'FIN450','WINTER',2006,'BLM212','10:30:00','987-65-4321','TTH');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(7777,'FIN480','SPRING',2006,'BLM305','13:30:00','765-43-2109','MW');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(8888,'IS320','SUMMER',2006,'BLM405','13:30:00','654-32-1098','MW');

INSERT INTO offering

     (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays)

     VALUES(9876,'IS460','SPRING',2006,'BLM307','13:30:00','654-32-1098','TTH');

DROP TABLE IF EXISTS Enrollment;

 

create table Enrollment (

OfferNo       INTEGER not null,

StdSSN        char(11) not null,

EnrGrade      decimal(3,2),

CONSTRAINT EnrollmentPK PRIMARY KEY (OfferNo, StdSSN)

/* CONSTRAINT OfferingFK FOREIGN KEY (OfferNo) REFERENCES Offering ON DELETE CASCADE, */

/* CONSTRAINT StudentFK FOREIGN KEY (StdSSN) REFERENCES Student ON DELETE CASCADE */

 )type=innodb;

 

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(1234,'123-45-6789',3.30);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(1234,'234-56-7890',3.50);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(1234,'345-67-8901',3.20);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(1234,'456-78-9012',3.10);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(1234,'567-89-0123',3.80);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(1234,'678-90-1234',3.40);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(4321,'123-45-6789',3.50);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(4321,'124-56-7890',3.20);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(4321,'789-01-2345',3.50);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(4321,'876-54-3210',3.10);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(4321,'890-12-3456',3.40);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(4321,'901-23-4567',3.10);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5555,'123-45-6789',3.20);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5555,'124-56-7890',2.70);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5678,'123-45-6789',3.20);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5678,'234-56-7890',2.80);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5678,'345-67-8901',3.30);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5678,'456-78-9012',3.40);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5678,'567-89-0123',2.60);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5679,'123-45-6789',2.00);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5679,'124-56-7890',3.70);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5679,'678-90-1234',3.30);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5679,'789-01-2345',3.80);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5679,'890-12-3456',2.9);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(5679,'901-23-4567',3.1);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(6666,'234-56-7890',3.1);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(6666,'567-89-0123',3.6);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(7777,'876-54-3210',3.4);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(7777,'890-12-3456',3.7);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(7777,'901-23-4567',3.4);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(9876,'124-56-7890',3.5);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(9876,'234-56-7890',3.2);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(9876,'345-67-8901',3.2);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(9876,'456-78-9012',3.4);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(9876,'567-89-0123',2.6);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(9876,'678-90-1234',3.3);

INSERT INTO enrollment(OfferNO, StdSSN, EnrGrade) VALUES(9876,'901-23-4567',4);

The MySQL Data Manipulation Language (DML)

SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes syntax to update, insert, and delete records. These query and update commands together form the Data Manipulation Language (DML) part of SQL:

• SELECT - extracts data from a database table

• UPDATE - updates data in a database table

• DELETE - deletes data from a database table

• INSERT INTO - inserts new data into a database table

1. Testing Rows Using the WHERE Clause

Retrieve the name, city and grade point average (GPA) of students, greater than or equal to 3.7

SELECT StdFirstName, StdLastName, StdCity, StdGPA

  FROM Student

  WHERE StdGPA >= 3.7;

2. Show all Columns

List all columns and rows of the Faculty table

SELECT * FROM Faculty;

-------------------------------------------------------------------------------------------------------------------

3. Inexact Matching with the LIKE Operator

List the senior level IS course

SELECT *

 FROM Course

 WHERE CourseNo LIKE 'IS4%';

4. Condition on Date Columns

List the name and hiring date of faculty hired in 1999 or 2000

SELECT FacFirstName, FacLastName, FacHireDate

 FROM Faculty

 WHERE FacHireDate BETWEEN '1999-01-01' AND '2000-12-31';

5. Complex Logical Expression

List the offer number, course number and faculty Social Security number for course offerings scheduled in fall 2005 or winter 2006

SELECT OfferNo, CourseNo, FacSSN

 FROM Offering

 WHERE (OffTerm = 'FALL' AND OffYear = 2005)

    OR (OffTerm = 'WINTER' AND OffYear = 2006);

6. Join Tables but Show Column from One Table Only

List the offering number, course number, days and time of offerings containing the words 'database' or 'programming' in the course description and taught in spring 2006. MySQL uses % as the wildcard character

SELECT OfferNo, Offering.CourseNo, OffDays, OffTime

 FROM Offering, Course

 WHERE OffTerm = 'SPRING' AND OffYear = 2006

   AND (CrsDesc LIKE '%DATABASE%'

    OR CrsDesc LIKE '%PROGRAMMING%')

   AND Course.CourseNo = Offering.CourseNo;

7. Join Tables and Show Columns from Both Tables

List the offer number, course number and name of the instructor of IS course offerings scheduled in fall 2005 taught by assistant professors

SELECT OfferNo, CourseNo, FacFirstName, FacLastName

 FROM Offering, Faculty

 WHERE OffTerm = 'FALL' AND OffYear = '2005'

   AND FacRank = 'ASST' AND CourseNo LIKE 'IS%'

   AND Faculty.FacSSN = Offering.FacSSN;

8. Join Tables Using a Join Operation in the FROM Clause

List the offer number, course number and name of the instructor of IS course offerings scheduled in fall 2005 that are taught by assistant professors. Similar result as previous query should be expected.

SELECT OfferNo, CourseNo, FacFirstName, FacLastName

 FROM Offering INNER JOIN Faculty

   ON Faculty.FacSSN = Offering.FacSSN

 WHERE OffTerm = 'FALL' AND OffYear = '2005'

   AND FacRank = 'ASST' AND CourseNo LIKE 'IS%';

9. Grouping on a Single Column

Summarize the averageGPA of students by major.

SELECT StdMajor, AVG(StdGPA) AS AvgGPA

 FROM Student

 GROUP BY StdMajor;

10. Counting Rows and Unique Column Values

Summarize the number of offerings and unique courses by year.

SELECT OffYear, COUNT(*) AS NumOfferings, COUNT(DISTINCT CourseNo) AS NumCourses

 FROM Offering

 GROUP BY OffYear;

11. Grouping with Row Conditions

Summarize the average GPA of upper-division (junior or senior) students by major.

SELECT StdMajor, AVG(StdGPA) AS AvgGpa

 FROM Student

 WHERE StdClass = 'JR' OR StdClass = 'SR'

 GROUP BY StdMajor;

Continue on part 2.

MySQL DML Practice 2:Part 1 |Part 2

<MySQL DML practice 1 | Main Java & Gang |MySQL DML practice 2 Part 2 >