< 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:

  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

 

 

Pre requirement for this tutorial: MySQL 5.x.x and NetBeans 6.x.x (or other MySQL query editor such as MySQL Query Browser etc.)

This exercise is based on the Database DESIGN, APPLICATION DEVELOPMENT & ADMINISTRATION, third edition reference book. The chapter is Chapter 4, though all the original scripts are in Microsoft Access 2000/2003 and Oracle 8i/9i, we have converted all of the SQL script to MySQL.

 

MySQL Data Definition Language (DDL)

 

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables. The most important DDL statements in SQL are:

 

  • CREATE TABLE - creates a new database table

  • ALTER TABLE - alters (changes) a database table

  • DROP TABLE - deletes a database table

  • CREATE INDEX - creates an index (search key)

  • DROP INDEX - deletes an index

 

Let create a database and tables. Then populate those tables with some sample data. Firstly, use MySQL Command Line Client. Create a database named tid5013practice. Then switch to NetBeans.

 

CREATE DATABASE tid5013practice;

 

1.      We will use NetBeans for database manipulation, however you can continue using the MySQL Command Line Client Tool to complete the following database DML exercise. Launch NetBeans and connect to the just created database as shown in the following Figures. Select Database > Driver > MySQL (Connector / J driver) > right click mouse > Select Connect Using.

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

DDL Exercises

 

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

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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');

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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 );

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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');

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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');

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

 

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);

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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:

 

 

  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;

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. Show all Columns

 

List all columns and rows of the Faculty table

 

SELECT * FROM Faculty;

 

 

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

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. Inexact Matching with the LIKE Operator

 

List the senior level IS course

 

SELECT *

 FROM Course

 WHERE CourseNo LIKE 'IS4%';

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. 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';

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. 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);

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. 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;

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. 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;

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. 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%';

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. Grouping on a Single Column

 

Summarize the averageGPA of students by major.

 

SELECT StdMajor, AVG(StdGPA) AS AvgGPA

 FROM Student

 GROUP BY StdMajor;

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. 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;

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

  1. 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;

 

MySQL and NetBeans - SQL database manipulation language (DML) practice screen shots

 

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 >