IT211 Structured Query Language (MySQL)

Assessment Rubric

• Test: 30%

• Project: 70% (NO Turnitin requirement)

Lesson Plan

Lesson 1: Introduction to Relational Database and SQL

Lesson 2: Getting Started

Lesson 3: Writing SQL Statements

Lesson 4: Filtering and Sorting Data

Lesson 5: Single Row Functions

Lesson 6: Multiple-row functions (Group Functions) and Group By clause

Lesson 7: Joining Tables

Lesson 8: Subqueries

Installation

During Installation your User Account Control (UAC) may block your installation.

Here's how to solve it

Chapter 1

SQL is used to interact with database.

Database is a collection of tables.

The tables in the database often consist of relationship between 1 another. 

Note: There is no need for every relation to have a relationship with every other relation. For example, in the above diagram, the Engineer table does not require a direct relationship to Software table. A join could be done via the Software-Engineer table. Refer to the Join chapter of your syllabus.

Create your database

XAMPP shuts down with error: "Exception EAccessViolation in module ..."

Solution:

• Go to XAMPP directory.

• Search for file xampp-control.ini

• Right-click to go to the properties of that file.

• Under security tab Select users Everyone.

• Edit permissions and provide full control.

• Apply

Chapter 2

Here are some examples of the Create Table statement:

CREATE TABLE IF NOT EXISTS `committee_members` 

(

  `PLAYERNO` int(11) NOT NULL,

  `BEGIN_DATE` date NOT NULL,

  `END_DATE` date DEFAULT NULL,

  `POSITION` char(20) DEFAULT NULL

);

CREATE TABLE PLAYERS

        (PLAYERNO       INTEGER      NOT NULL,

         PNAME                 CHAR(15)     NOT NULL,

         INITIALS            CHAR(3)      NOT NULL,

         BIRTH_DATE     DATE,

         SEX                      CHAR(1)      NOT NULL,

         JOINED               SMALLINT     NOT NULL,

         STREET              VARCHAR(30)  NOT NULL,

         HOUSENO           CHAR(4),

         POSTCODE          CHAR(6),

         TOWN                 VARCHAR(30)  NOT NULL,

         PHONENO          CHAR(13),

         LEAGUENO         CHAR(4),

         PRIMARY KEY    (PLAYERNO) );

Additional Notes about creating Primary Key and Composite Primary Key using phpMyAdmin:

Here's an example of the Insert statement:

INSERT INTO `committee_members` (`PLAYERNO`, `BEGIN_DATE`, `END_DATE`, `POSITION`) VALUES

(2, '1990-01-01', '1992-12-31', 'Chairman'),

(2, '1994-01-01', NULL, 'Member'),

(6, '1990-01-01', '1990-12-31', 'Secretary'),

(6, '1991-01-01', '1992-12-31', 'Member'),

(6, '1992-01-01', '1993-12-31', 'Treasurer'),

(6, '1993-01-01', NULL, 'Chairman'),

(8, '1990-01-01', '1990-12-31', 'Treasurer'),

(8, '1991-01-01', '1991-12-31', 'Secretary'),

(8, '1993-01-01', '1993-12-31', 'Member'),

(8, '1994-01-01', NULL, 'Member'),

(27, '1990-01-01', '1990-12-31', 'Member'),

(27, '1991-01-01', '1991-12-31', 'Treasurer'),

(27, '1993-01-01', '1993-12-31', 'Treasurer'),

(57, '1992-01-01', '1992-12-31', 'Secretary'),

(95, '1994-01-01', NULL, 'Treasurer'),

(112, '1992-01-01', '1992-12-31', 'Member'),

(112, '1994-01-01', NULL, 'Secretary');

Extra notes: for adding record using the phpMyAdmin form into MySQL.

Here's an example of the Delete statement:

DELETE FROM `grades` WHERE `grade` = 'B'

Data Dictionary using PhpMyAdmin

You can get the sql script from me to import into your database. 

But make sure you are in a database before performing the actual import.

Chapter 3

Projection is the selection of column/s to retrieve.

The "Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available." message is NOT an error. It is just a warning message.

Chapter 5

Concatenation - CONCAT()

Use this function to gain the concatenation marks in your project.

Date Function

Now() function: Returns the system current date and time.

Example of SQL Statement:

    Select Now();

Output

Now()

2017-09-22 14:19:52   [YYYY-MM-DD HH:MM:SS] *HH is in 24-hour format.

DayOfWeek() function: Returns a numeric value of 1-7 (where 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday ,6=Friday, 7=Saturday)

Example of SQL Statement:

Select Now(), DayOfWeek(Now());

Output

Now()                                       | DayOfWeek(Now())

2017-09-22   14:23:06  |   6

Month() Function

Month function can be used to extract the Month portion from a Date column.

SELECT NAME, BIRTH_DATE, MONTH(BIRTH_DATE) FROM players 

Here's a combination of the Month function and the Now function:

SELECT NAME, BIRTH_DATE, MONTH(BIRTH_DATE) FROM players  WHERE MONTH(BIRTH_DATE) = MONTH(Now())

Chapter 6: Group Function

The HAVING clause is used to select rows after a GROUP BY clause has been executed

Chapter 7: Join

Syntax for join:

SELECT table1.column1, table2.column2

FROM table1, table2

WHERE table1.column1 = table2.column2;

Additional Notes:

Misc.

Copy table to another database.

Error message when dropping a database in XAMPP - Solution

Best regards,