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