MYSQL : SQL REVISION TOUR (CLASS - XII) PART - 2

 MYSQL: SQL REVISION TOUR

CLASS – XII (PART - II)

CHAPTER – 5

Creating Tables in MYSQL

Tables are defined with the CREATE TABLE command. When a table is created, its columns are named, data types and size are supplied for each column. Each table must have at least one column.

Syntax of CREATE TABLE command:

CREATE TABLE <table-name>

                (<column name><data type> [(<size>)],

                (<column name><data type> [(<size>)……..];

Example: To create employee table whose schema is as follows:

                Employee (ecode, name, gender, grade, gross)

The command will be:

CREATE TABLE EMPLOYEE

                (              ECODE                  INTEGER,

                                ENAME                 CHAR(20),

                                GENDER               CHAR(1),

                                GRADE                  VARCHAR(2),

                                GROSS                  DECIMAL  ) ;

Note: Before issuing a CREATE TABLE command, make sure that its parent database has been opened using USE <database name> command.

Inserting Data into Table:

The rows (tuples) are added to relations using INSERT command of SQL. In its simplest form, INSERT takes the following syntax:

                INSERT INTO <table name> [<column list>]

                VALUES (<value>, <value>, ……)  ;

Example:

                INSERT INTO EMPLOYEE

                VALUES ( 1001, ‘SHEKHAR’, ‘M’, ‘E4’, 5462.00) ;

Inserting NULL values

To insert value NULL in a specific column, you can type NULL without quotes and NULL will be inserted in the column.

Consider the following statement:

                INSERT INTO EMPL (Enpno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)

                VALUES( 5400, ‘KARAN’, ‘ANALYST’, NULL, ‘2005-10-25’, 8500, NULL, 20) ;

See, for Mgr and Comm columns, NULL values have been inserted.

Inserting Date

Dates are by default entered in ‘YYYY-MM-DD’ format i.e., first four digit depicting year, followed by a hyphen, followed by 2 digit of month, followed by a hyphen and a two digit day. All this is enclosed in single quotes.

Making Queries Through SELECT Command:

If your table is employee

1. Select All Data

mysql> SELECT * FROM EMPLOYEE ;

2. Selecting Particular Rows

mysql>SELECT * FROM EMPLOYEE WHERE GENDER = ‘M’ ;

OR

mysql>SELECT * FROM EMPLOYEE WHERE HIREDATE >= ‘2005-1-1’ ;

3. Selecting Particular Columns

You can select particular columns by specifying columns-name (i.e. attributes) in the select-list of the SELECT command e.g.,

Display name and hiredate of all employees.

                                mysql> SELECT NAME, HIREDATE FROM EMPLOYEE;

4. Eliminating Redundant Data (with Keyword DISTINCT)

By default, data is selected from all the rows of the table, even if the data appearing in the result gets duplicated. The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement. For Example:

Display name of the all employees (non-redundant).

                mysql>SELECT DISTINCT NAME FROM EMPLOYEE;

5. Viewing Structure of a Table

If you want to know the structure of a table, you can use Describe or Desc command as per following syntax:

                DESCRIBE|DEST <TABLE NAME> ;

Example              mysql> DESC EMPLOYEE ;        OR          mysql> DESCRIBE EMPLOYEE ;

6. Performing Simple Calculations

                (i) To calculate 3.14143 * 7*7

                                mysql> SELECT 3.14143 * 7 *7 ;

                (ii) To obtain current system date

                                mysql>SELECT  curdate () ;

7. Using Column Aliases

The column that you select in a query can be given a different name i.e., column alias name for output purpose.

Example:

                mysql> SELECT HIREDATE, TYPE AS “DATE OF JOIN” FROM EMPLOYEE ;

8. Condition Based on a Range

The BETWEEN operator defines a range of values that the column values must fall in to make the condition true. The range includes both lower value and the upper value. For example, to list the item whose range between 40 to 60 (both inclusive), the command would be:

    mysql> SELECT ICODE, DESCP, RANGE FOR ITEM WHERE RANGE BETWEEN 40 AND 60 ;

9. Condition Based on a List

To specify a list of values, IN operator is used. The IN operator selects values that match any value in a given list of values. For example, to display a list of members for ‘DELHI’, ‘MUMBAI’, ‘CHENNAI’, ‘BANGLORE’ cities, you may give

                mysql> SELECT * FROM MEMBERS

                                WHERE CITY IN (‘DELHI’, ‘MUMBAI’, ‘CHENNAI’, ‘BANGLORE’) ;

The NOT IN operator finds rows that do not match in the list.

                mysql> SELECT * FROM MEMBERS

                                WHERE CITY NOT IN (‘DELHI’, ‘MUMBAI’, ‘CHENNAI’) ;

10. Condition Based on Pattern Matches

SQL also includes a string-matching operator LIKE, for comparisons on character string using patterns. Patterns are describe using two special wildcard characters:

                percent (%) : The % character matches any substring.

                Underscore ( _ ) : The _ character matches any character.

The LIKE keyword is used to select rows containing columns that match a wildcard pattern.

Example:

1. To list members which are in areas with pin code starting with 13, the command is:

                mysql> SELECT FIRSTNAME, LASTNAME, CITY FROM MEMBERS WHERE PIN LIKE ‘13%’ ;

2. To list names of pets who have name ending with ‘y’, the command would be:

                mysql> SELECT NAME FOR PET WHERE NAME LIKE ‘%y’ ;

11. Searching for NULL

The Null value in a column can be searched for in a table using IS NULL in the WHERE  clause. (Relational operators like =, <> etc. can’t be used with NULL). For example, to list details of all employees whose departments contain NULL (i.e. novalue), you use the command:

                mysql> SELECT EMPNO, EMPNAME, JOB FROM EMP WHERE DEPTNO ISNULL ;

Non-NULL values in a table can be listed using IS NOT NULL.

Creating Tables with SQL Constraints

If we are to create the customer table, we would type in

                CREATE TABLE CUSTOMER

                (              FIRST_NAME CHAR(40),

                                LAST_NAME CHAR(40),

                                ADDRESS CHAR(70),

                                CITY CHAR(60),

                                COUNTRY CHAR(40),

                                BIRTH_DATE DATE           ) ;

SQL Constraints

SQL NOT NULL Constraint

By default, a column can hold NULL. If you do not want to allow NULL value in a column you will want to place a constraint on this column specifying that NULL is now not an allowable value.

Example:             CREATE TABLE CUSTOMER

                                (              SID         INTEGER              NOT NULL,

                                                LAST_NAME       VARCHAR(40) NOT NULL,

                                                FIRST_NAME     VARCHAR(30)  )  ;

In the above table, SID and LAST_NAME cannot include NULL, while FIRST_NAME can include NULL.

SQL DEFAULT Constraint

The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

Example:                             CREATE TABLE STUDENT

                                                (              STUDENT_ID      INT                         UNIQUE,

                                                                LAST_NAME       VARCHAR(50),

                                                                FIRST_NAME     VARCHAR(50),

                                                                SCORE                   DEFAULT  60    )  ;

SQL UNIQUE Constraint

The UNIQUE constraint ensure that all values in a column are distinct. In other words, no two row can hold the same value for a column with UNIQUE constraint.

Example:                             CREATE TABLE STUDENT

                                                (              STUDENT_ID      INT                         UNIQUE,

                                                                LAST_NAME       VARCHAR(50),

                                                                FIRST_NAME     VARCHAR(50),

                                                                SCORE                   DEFAULT  60    )  ;

SQL CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy certain condition. Once defined, the database will only insert a new row or update an existing row if the new value satisfies the CHECK constraint. The CHECK constraint is used to ensure data quality.

Example              CREATE TABLE CUSTOMER

                                (              SID         INT         CHECK (SID>0),

                                                FIRST_NAME     VARCHAR (40),

                                                LAST_NAME       VARCHAR(40)  )  ;

Note: MYSQL does not enforce the CHECK constraint although MYSQL syntax allows the definition of  CHECK constraint. As per MYSQL documentation, ‘Check constraint gets parsed but IGNORED’.

Primary Key and Foreign Key Constraint

Whenever two tables are related by a common column (or set of columns), then the related columns in the parent table (or primary table) should be either declared a PRIMARY KEY or UNIQUE key and the related columns in the child table (or related table) should have FOREIGN KEY constraint. For instance, if we have two tables having structures as given below:

In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.

Applying Table Constraints

When a constraint is to be applied on a group of columns of the table, it is called table constraint. The table constraints appear in the end of table definition. For instance, if you want combination of icode and descp of table items to be unique, you may write it as follows:

                CREATE TABLE ITEMS

                (              ICODE                   CHAR(6)               NOT NULL,

                                DESCP                   CHAR(30)             NOT NULL,

                                ROL                        INT,

                                QOH                      INT,

                                CHECK                   (ROL < QOH),

                                UNIQUE               (ICODE, DESCP)  ) ;

Viewing a Table Structure

If your table name is EMPL, then type

Example                              DESC EMPL ;       OR          DESCRIBE EMPL ;

Inserting Data into another Table

If your table name is EMPL, the use the command

                INSERT INTO EMPL VALUES ( 5000, ‘RAMESH’, ‘ M’, ‘E4’, 4500.00) ;

Modifying Data in Tables

If table name ITEMS, then use the command

                UPDATE ITEMS SET ROL = 250;

To update the ROL and QOH for items having icode less than ‘I040’, we shall write

                UPDATE ITEMS SET ROL = 400, QOH = 700 WHERE ICODE < ‘I040’ ;

To double the gross pay of empl of grade ‘E3’ and ‘E4’, you use the command:

            UPDATE EMPL SET GROSS = GROSS * 2 WHERE (GRADE = ‘E3’ OR GRADE = ‘E4’) ;

Deleting Data from Tables

To delete some data from table, you can use SQL DELETE commands. The  DELETE command removes rows from a table. This removes the entire rows, not individual field values, so no field argument is needed or accepted.

If you remove the tuples form empl table that have gross(salary) less than 3200.00 the following command is used:

                DELETE FROM EMPL WHERE GROSS <3200.00 ;

Altering Tables

The  ALTER TABLE command is used to change definitions of existing tables. Usually, it is used to add columns to a table. Sometimes it is used to delete columns or change their sizes. In general, in MYSQL SQL, ALTER TABLE command is used:

·         To add a column

·         To add an integrity constraint

·         To redefine a column (datatype, size, default value)

Example

1. To add new column tel_number of type integer in table EMPL you may give:

                ALTER TABLE EMPL ADD (TEL_NUMBER INT) ;

2. To modify column job of table EMPL to have new width of 50 characters, you may give:

                ALTER TABLE EMPL MODIFY (JOB CHAR(50) ) ;

3. To change the name of one of your columns. For this you can use CHANGE clause of ALTER TABLE command. To change the existing column namely FIRST_NAME of table of table STUDENT,  to FIRSTNAME you may write:

                ALTER TABLE CUSTOMER CHANGE FIRST_NAME FIRSTNAME VARCHAR(20) ;

Dropping Tables

The DROP TABLE command of SQL lets you drop a table from the database. If Your table name is  EMPL then, you can use the command:

                DROP TABLE EMPL ;

SQL Joins

An SQL Join is a query that fetches data from two or more tables whose records are joined with one another based on a condition.

Example:

                mysql> SELECT ENAME, LOC FROM EMPL, DEPT WHERE ENAME = ‘ANOOP’ AND

                                EMPL.DEPTNO = DEPT.DEPTNO ;

The above SQL statement will first join the tables EMPL and DEPT on the basis of join condition EMPL.DEPTNO = DEPT.DEPTNO. That is a record from table EMPL will join with a record from DEPT table whose DEPTNO is equal to its DEPTNO.

There can be many types of JOINS possible. These are:

1. Cartesian Product:An SQL join query without any join condition returns all the records of joined with all the records of the other table. It is known as a Cartesian Product, This type of join is also known as a CROSS JOIN.

2. Equi Join:An SQL join query that joins two or more tables based on a condition using equality operator.

3. Inner Join:An INNER JOIN implements an equi join. In this join, only those rows are returned from both the tables that satisfy the join condition. The join conditions can match records based on one or more columns.

4. Natural Join:A NATURAL JOIN is a type of equi join where the join condition compares all the same names columns in both tables.

5. Let Join:The LEFT JOIN  is a particular type of join that selects rows from both left and right tables that are matched, plus all rows from the left table (Table A) even with no matching rows found in theright table (Table B).

6. Right Join:The RIGHT JOIN is a particular type of join that selects rows from both left and right tables that are matched, plus all rows from the right table (Table B) even with no matching rows found in the left table (Table A).

NOTE: This is the notes of MYSQL REVISION TOUR (Second Part). It is important for every students, who are going to appear in Class - XII CBSE BOARD Exam as well as competitive level exam.

!!! THANK YOU !!!