CLASS XII
INFORMATICS PRACTICES
CHAPTER 13
MY SQL REVISION TOUR
Data:
Basic/raw facts about something which is not organized, for example details of some students which is not
organized.
Data Item:
Each piece of information about an entity, such as name of a person or address, age or name of a product or the
price is a Data Item.
Database:
A well organised collection of data that ensures safety, security and integrity of data.
DataBase Management System(DBMS)
Comprehensive software that provides the essential services to create, manage and maintain the databases. In
short a DBMS provides the means to store the data in the database, to edit or delete the data stored, to search
and analyze the data in the database. They also provide various safety and security mechanisms that ensures that
in any case stored data will be safe and accessible.
Database Systems:
Systems comprising of Databases and Database Management Systems are simply referred as database systems.
Advantages of Data Base System:
1) Reduce data redundancy (duplication of data)
2) Control data inconsistency to a large extent
3) Database facilitate sharing of data
4) Enforce standards
5) Centralized databases can ensure data security
Examples of Common Database Management Systems:
MySQL ,INGRES, POSTGRES, ORACLE, DB2.
Data Model
A data model refers to a set of concepts to describe the structure of a database , and certain constraints that the database should obey.
The four data models that are used for database management are :
Relational Data Model
Hierarchical Data Model
Hierarchical Data Model
Network Data Model
Object Oriented Data Model
Relational data moel :
The most popular data model in DBMS is the Relational Model. It is more scientific a model than others. This model is based on first-order predicate logic and defines a table as an n-ary relation. In relational model data is organized in the form of tables called relations.
The main highlights of this model are −
- Data is stored in tables called relations.
- Relations can be normalized.
- In normalized relations, values saved are atomic values.
- Each row in a relation contains a unique value.
- Each column in a relation contains values from a same domain.
Network Model
In the network model, entities are organised in a graph,in which some entities can be accessed through several path. The network model differs from the relational model in that data is represented by collections of records and relationships among data are represented by links . A record is a collection of fields (attributes),each of which contains only one data value. By a link we mean that it as an association between precisely two records .
Hierarchical Model
In this model each entity has only one parent but can have several children . At the top of hierarchy there is only one entity which is called Root.The records have 1:N relationship. The data is represented by collections of records and relationships between among data represented by links . Hierarchical model has a tree like structure
Object Oriented Data Model
In object oriented data model, data associated operations are represented by objects. An object is an identifiable entity with some characteristics and behavior. Similar objects are conceptually collected together into meaningful groups called classes. e.g. “Mohan” object belongs to “student” class. Objects of the same classes have common attributes, behaviors and relationships with other objects.
Essential features of OO data model are :
Object entity : The ability of a system to distinguish between two different objects that have same state
Encapsulation : The ability to wrap up data and associated operations under one unit .
Genericity : It means that the types of datamodel with which the object query language collaborates must be generic I e a new type is added to the system, it must be compatible and queriable.
Basics of Relational Model
Relation :
A tabular structure containing data. To be a relation is must satisfy following four conditions:
Atomicity : At every row-column intersection (Cell) there must be an atomic value
i.e. a value that can not be further subdivided.
No duplicity: No two rows of relation will be identical i.e. in any two rows value in
at least one column must be different.
Ordering of rows is immaterial.
Ordering of columns is immaterial.
Tuple :
A row in a relation is called a tuple
Attribute :
A column in a relation is called an attribute
Domain :
Domain of an attribute refers to the set of all the possible values for that attribute.
Degree :
Number of attributes in a relation is the degree of that relation
Cardinality :
Number of tuples in a relation is the cardinality of that relation.
Candidate Key:
A set of one or more minimal attributes used to uniquely identify a tuple in the relation
and which can act as Primary Key. A relation can have multiple candidate keys
Primary Key:
A candidate key that is primarily chosen for unique identification of tuples in a Relation.
Any subset of Primary key should not be Primary key.
Alternate Key:
Candidate keys that not chosen as primary key are the alternate keys.
Foreign Key:
Can A non key attribute whose values are derived from the primary key of some other table , is known as foreign key in its current table .
Example:
In A LIBRARY Table
Candidate keys can be Accession no, Book no,
Primary key: If we select Book no as primary key for our purpose then
Alternate Key will be Accession No.
Views : A view is a virtual table whose contents are taking from other tables depending upon a condition.
Table: Student
|
Roll No. Name Marks
|
101 Anu 85
|
102 Riya 70
|
103 Ankit 78
|
Definition of the VIEW : CREATE VIEW toppers AS
SELECT * FROM Student
WHERE Marks > 75 ;
Here name of the view is toppers
Base table is students
toppers( A virtual table based on Student table)
Roll No. Name Marks
|
101 Anu 85
|
103 Ankit 78
|
Referential Integrity :
Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Thus, any primary key field changes must be applied to all foreign keys, or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key.
For example, if one deletes a donor from the
Donor table, without also deleting the corresponding donations from the Donation table, then the DonorID field in the Donation record would refer to a non-existent donor. In later chapters, we will discuss a few mechanisms that can be used to enforce referential integrity, including triggers, constraints, transactions, and stored procedures.
MySQL is an open source Relational Database Management System. MySQL is very fast reliable and flexible Database Management System. It provides a very high performance and it is multi-threaded and multi user Relational Database management system.
MySQL was created and supported by MySQL AB, a company based in Sweden.
MySQL Features
- MySQL are very fast and much reliable for any type of application.
- MySQL is very Lightweight application.
- MySQL command line tool is very powerful and can be used to run SQL queries against database.
- MySQL supports indexing and binary objects.
- It is allow changes to structure of table while server is running.
- MySQL has a wide user base.
- It is a very fast thread-based memory allocation system.
8. MySQL Written in C and C++ language.
9. MySQL code is tested with different compilers.
10. MySQL is available as a separate program for use in a client/server network environment.
MySQL Server : An object relational database management system that provides an open , comprehensive and integrated approach to information management.
MySQL Instance : Software executing on MySQL Server , to provide access to information stored in the database .
Advantages of MySQL:
Reliability and Performance : MySQL is very reliable and high performance relational database management system. It can used to store many GB's of data into database.
Availability of Source: MySQL source code is available that's why now you can recompile the source code.
Cross-Platform support: MySQL supports more then twenty different platform including the major Linux distribution .Mac OS X, Unix and Microsoft windows.
Large pool of Trained and Certified Developers: MySQL is very popular and it is world most popular open source Database. So it is easy to find high quality staff around the world.
Powerful Uncomplicated software: The MySQL has most capabilities to handle most corporate database application and used to very easy and fast
Reliability and Performance : MySQL is very reliable and high performance relational database management system. It can used to store many GB's of data into database.
Availability of Source: MySQL source code is available that's why now you can recompile the source code.
Cross-Platform support: MySQL supports more then twenty different platform including the major Linux distribution .Mac OS X, Unix and Microsoft windows.
Large pool of Trained and Certified Developers: MySQL is very popular and it is world most popular open source Database. So it is easy to find high quality staff around the world.
Powerful Uncomplicated software: The MySQL has most capabilities to handle most corporate database application and used to very easy and fast
Starting MySQL
MySQL Data Types
In MySQL there are three main data types: text, number, and Date/Time.
Text types:
Text types:
Data type
|
Description
|
CHAR(size)
|
Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
|
VARCHAR(size)
|
Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
|
TINYTEXT
|
Holds a string with a maximum length of 255 characters
|
TEXT
|
Holds a string with a maximum length of 65,535 characters
|
BLOB
|
For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
|
MEDIUMTEXT
|
Holds a string with a maximum length of 16,777,215 characters
|
MEDIUMBLOB
|
For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
|
LONGTEXT
|
Holds a string with a maximum length of 4,294,967,295 characters
|
LONGBLOB
|
For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
|
ENUM(x,y,z,etc.)
|
Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
Note: The values are sorted in the order you enter them.
You enter the possible values in this format: ENUM('X','Y','Z') |
SET
|
Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice
|
Number types:
Data type
|
Description
|
TINYINT(size)
|
-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
|
SMALLINT(size)
|
-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
|
MEDIUMINT(size)
|
-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
|
INT(size)
|
-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
|
BIGINT(size)
|
-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
|
FLOAT(size,d)
|
A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
|
DOUBLE(size,d)
|
A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
|
DECIMAL(size,d)
|
A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
|
*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.
Date types:
Date types:
Data type
|
Description
|
DATE()
|
A date. Format: YYYY-MM-DD
Note: The supported range is from '1000-01-01' to '9999-12-31'
|
DATETIME()
|
*A date and time combination. Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
|
TIMESTAMP()
|
*A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
|
TIME()
|
A time. Format: HH:MM:SS
Note: The supported range is from '-838:59:59' to '838:59:59'
|
YEAR()
|
A year in two-digit or four-digit format.
Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069
|
*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.
Difference between Char and Varchar
The difference between char an varchar is that char is of fixed length and Varchar is of variable length . So using Varchar saves memory space if we don’t know how many bytes we are going to use.
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.
Below is an example of a table called "Persons":
Below is an example of a table called "Persons":
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
The table above contains three records (one for each person) and five columns (P_Id, LastName, FirstName, Address, and City).
RDBMS
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
Keep in Mind That...
- SQL is not case sensitive
Classification of SQL statements
SQL provides many different types of commands used for different purposes. SQL can be divided into following parts:
1. Data Manipulation Language (DML) commands
2. Data Definition Language (DDL) commands.
3. Transaction Control Language (TCL) commands.
DATA DICTIONARY:
A data dictionary is a file that contains “metadata” i.e. Data about data .
Whenever data is read or modified in the database system , the data dictionary is consulted .
DML commands: A DML is a language that enables users to access on manipulates data as organized by the appropriate data model. The query and update commands form the DML part of SQL:
- SELECT - extracts data from a database
- UPDATE - updates data in a database
- DELETE - deletes data from a database
- INSERT INTO - inserts new data into a database
DDL commands: The DDL commands, as the name suggests, allow you to perform tasks related to data definition. The DDL part of SQL permits database tables to be created or deleted. The most important DDL statements in SQL are:
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies a database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies a table
- DROP TABLE - deletes a table
TCL commands: The TCL commands used to manage and control the transactions of data in database. The most important TCL commands are:
- COMMIT – it make all the changes made by statement issued.
- ROLLBACK – it undoes all changes since the beginning of the transaction or since save point.
- SAVEPOINT – it marks a point upto successfully completed transaction.
- SET TRANSACTION – it establish properties for the current transaction.
· Before you start creating tables in MySQL we need to create the database . A database is the container for all the tables . It acts as a central point of administration for the tables in the database . The actual data is stored in the table.
CREATING A DATABASE
SYNTAX : CREATE DATABASE <DATABASE NAME> ;
Eg. To create a database employee the syntax will be
CREATE DATABASE EMPLOYEE;
OPENING A DATABSE
Creating a database is not sufficient . We need to open the database before creating a table.
SYNTAX : USE<DATABASE NAME>
e.g. to use database employee the syntax will be
USE EMPLOYEE;
REMOVING DATABASES
The command used to remove a database has the following syntax :
SYNTAX : DROP <DATABASE NAME>;
e.g. If I want to remove the database employee . The syntax of the command will be the following :
DROP DATABASE EMPLOYEE
MySQL Data Types:
Every column (or data item) should belong to a unique domain (known as data type). These data types help to describe the kind of information a particular column holds. MySQL supports the ANSI SQL data types. Some of the commonly used data types along with their characteristics are as follows:
Class
|
Data Type
|
Description
|
Example
|
Text
|
CHAR(size)
|
A fixed-length string between 1 and 255 characters in length right-padded with spaces to the specified length when stored. Values must be enclosed in single quotes or double quotes.
|
‘Maths’ ‘TexT’
|
VARCHAR(size)
|
A variable-length string between 1 and 255 characters in length; for example VARCHAR(25). Values must be enclosed in single quotes or double quotes
|
‘Computer’ ‘Me and u’
| |
NUMERIC
|
DECIMAL(p,s)
|
It can represent number with or 17.3 without the fractional part. The size argument has two parts: precision and scale. Precision (p) indicates the number of significant digits and scale (s) maximum number of digits to the right of the decimal point
|
987.98
|
INTEGER
|
It is used for storing integer values
|
345
| |
Date
|
DATE
|
It represents the date including day, month and year between 1000-01-01 and 9999-
12-31
|
2009-07-02
|
Creating Tables
Tables are defined with the CREATE TABLE command. When tables are created its columns are named, data types and sizes supplied for each column. At least one column must be specified.
Syntax:
CREATE TABLE <TableName>(<ColumnName1> <Data Type1>,
<ColumnName2> <Data Type2>,….. ….,<ColumnNameN> <Data Type N>);
CREATE TABLE Example
Now we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName, Address, and City.
We use the following CREATE TABLE statement:
We use the following CREATE TABLE statement:
CREATE TABLE Persons
(
P_Id integer,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
(
P_Id integer,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
The P_Id column is of type int and will hold a number. The LastName, FirstName, Address, and City columns are of type varchar with a maximum length of 255 characters.
The empty "Persons" table will now look like this:
The empty "Persons" table will now look like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
The empty table can be filled with data with the INSERT INTO statement.
Eg INSERT INTO PERSONS VALUES (1,’RAHUL’,’GUPTA’,’12 /31 Punjabi Bagh ’,’Delhi’)
SIMPLE QUERIES IN SQL
SIMPLE QUERIES IN SQL
Accessing Database
USE DATABASENAME
eg . if I want to use database employee .. the command will be
USE DATABASE EMPLOYEE
Viewing Table Structure:
DESC <TABLE NAME >
SQL SELECT Statement
The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. Syntax
SELECT column_name(s)
FROM table_name |
and
SELECT * FROM table_name
|
An SQL SELECT Example
The "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to select the content of the columns named "LastName" and "FirstName" from the table above.
We use the following SELECT statement:
We use the following SELECT statement:
SELECT LastName, FirstName FROM Persons
|
The result-set will look like this:
LastName
|
FirstName
|
Hansen
|
Ola
|
Svendson
|
Tove
|
Pettersen
|
Kari
|
SELECT * Example
Now we want to select all the columns from the "Persons" table. We use the following SELECT statement:
SELECT * FROM Persons
|
Tip: The asterisk (*) is a quick way of selecting all columns!
The result-set will look like this:
The result-set will look like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
The DISTINCT keyword can be used to return only distinct (different) values.
SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name |
SELECT DISTINCT Example
The "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to select only the distinct values from the column named "City" from the table above.
We use the following SELECT statement:
We use the following SELECT statement:
SELECT DISTINCT City FROM Persons
|
The result-set will look like this:
City
|
Sandnes
|
Stavanger
|
The WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax
SELECT column_name(s)
FROM table_name WHERE column_name operator value |
WHERE Clause Example
The "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to select only the persons living in the city "Sandnes" from the table above.We use the following SELECT statement:
SELECT * FROM Persons
WHERE City='Sandnes' |
The result-set will look like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
Quotes Around Text Fields
SQL uses single quotes around text values (most database systems will also accept double quotes). Although, numeric values should not be enclosed in quotes. For text values:
This is correct:
SELECT * FROM Persons WHERE FirstName='Tove' This is wrong: SELECT * FROM Persons WHERE FirstName=Tove |
For numeric values:
This is correct:
SELECT * FROM Persons WHERE Year=1965 This is wrong: SELECT * FROM Persons WHERE Year='1965' |
Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:
Operator
|
Description
|
=
|
Equal
|
<>
|
Not equal
|
>
|
Greater than
|
<
|
Less than
|
>=
|
Greater than or equal
|
<=
|
Less than or equal
|
BETWEEN
|
Between an inclusive range
|
LIKE
|
Search for a pattern
|
IN
|
If you know the exact value you want to return for at least one of the columns
|
Note: In some versions of SQL the <> operator may be written as !=
The AND & OR Operators
The AND & OR operators are used to filter records based on more than one condition. The AND operator displays a record if both the first condition and the second condition is true. And OR operator displays a record if either the first condition or the second condition is true.
AND Operator Example
The "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to select only the persons with the first name equal to "Tove" AND the last name equal to "Svendson":
We use the following SELECT statement:
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName='Tove' AND LastName='Svendson' |
The result-set will look like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
OR Operator Example
Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to "Ola":
We use the following SELECT statement:
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName='Tove' OR FirstName='Ola' |
The result-set will look like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
Combining AND & OR
You can also combine AND and OR (use parenthesis to form complex expressions). Now we want to select only the persons with the last name equal to "Svendson" AND the first name equal to "Tove" OR to "Ola":
We use the following SELECT statement:
We use the following SELECT statement:
SELECT * FROM Persons WHERE
LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola') |
The result-set will look like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
The ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by a specified column.The ORDER BY keyword sort the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword.
SQL ORDER BY Syntax
SELECT column_name(s)
FROM table_name ORDER BY column_name(s) ASC|DESC |
ORDER BY Example
The "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
4
|
Nilsen
|
Tom
|
Vingvn 23
|
Stavanger
|
Now we want to select all the persons from the table above, however, we want to sort the persons by their last name.
We use the following SELECT statement:
We use the following SELECT statement:
SELECT * FROM Persons
ORDER BY LastName |
The result-set will look like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
4
|
Nilsen
|
Tom
|
Vingvn 23
|
Stavanger
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
ORDER BY DESC Example
Now we want to select all the persons from the table above, however, we want to sort the persons descending by their last name.
We use the following SELECT statement:
We use the following SELECT statement:
SELECT * FROM Persons
ORDER BY LastName DESC |
The result-set will look like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
4
|
Nilsen
|
Tom
|
Vingvn 23
|
Stavanger
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
Performing Simple Calculations :
mysql> SELECT 3*4;
Using Column Alias
The columns that you select in a query can be given different name i.e. column alias name for output purposes
Eg
mysql> SELECT date , type as “Event Type”
from event;
Condition based on a range
The BETWEEN operator defines a range of values that column must fall in to make the condition true.
Eg select icode, descp , QOH
From items
Where QOH BETWEEN 30 AND 50
Condition base on list
To specify a list of values , IN operator is used .The IN operator selects the values that match any value in a given list of values . For example , to display a list of members from ‘DELHI’ ,’CHENNAI’ or ‘BANGLORE’ cities , you may give
SELECT * FROM MEMBERS
WHERE city IN (‘DELHI’ ,’CHENNAI’, ‘BANGLORE’ )
Condition based on Pattern Matches
Percent (%)
Underscore ( _ )
SQL Functions
STRING FUNCTIONS
The string functions in MySQL can manipulate the text string in many ways. Some commonly used string functions are the following:
1. CHAR(N,... [USING charset_name])
CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.
Char() Function returns the character value for each integer passed
mysql> SELECT CHAR(77,121,83,81,'76');
+---------------------------------------------------------+
| CHAR(77,121,83,81,'76') |
+---------------------------------------------------------+
| MySQL |
+---------------------------------------------------------+
1 row in set (0.00 sec)
2. CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:
mysql> SELECT CONCAT('My', 'S', 'QL');
+---------------------------------------------------------+
| CONCAT('My', 'S', 'QL') |
+---------------------------------------------------------+
| MySQL |
+---------------------------------------------------------+
1 row in set (0.00 sec)
3. LCASE(str)
LCASE() is a synonym for LOWER()
4. LOWER(str)
Returns the string str with all characters changed to lowercase according to the current character set mapping.
mysql> SELECT LOWER('QUADRATICALLY');
+---------------------------------------------------------+
| LOWER('QUADRATICALLY') |
+---------------------------------------------------------+
| quadratically |
+---------------------------------------------------------+
1 row in set (0.00 sec)
5. SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.
mysql> SELECT SUBSTRING('Quadratically',5);
+---------------------------------------------------------+
| SUBSTRING('Quadratically',5) |
+---------------------------------------------------------+
| ratically |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
+---------------------------------------------------------+
| SUBSTRING('foobarbar' FROM 4) |
+---------------------------------------------------------+
| barbar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('Quadratically',5,6);
+---------------------------------------------------------+
| SUBSTRING('Quadratically',5,6) |
+---------------------------------------------------------+
| ratica |
+---------------------------------------------------------+
1 row in set (0.00 sec)
6. UCASE(str)
UCASE() is a synonym for UPPER().
7. UPPER(str)
Returns the string str with all characters changed to uppercase according to the current character set mapping.
mysql> SELECT UPPER('Allah-hus-samad');
+---------------------------------------------------------+
| UPPER('Allah-hus-samad') |
+---------------------------------------------------------+
| ALLAH-HUS-SAMAD |
+---------------------------------------------------------+
1 row in set (0.00 sec)
8. LTRIM(str)
Returns the string str with leading space characters removed.
mysql> SELECT LTRIM(' barbar');
+---------------------------------------------------------+
| LTRIM(' barbar') |
+---------------------------------------------------------+
| barbar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
9. RTRIM(str)
Returns the string str with trailing space characters removed.
mysql> SELECT RTRIM('barbar ');
+---------------------------------------------------------+
| RTRIM('barbar ') |
+---------------------------------------------------------+
| barbar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
10. TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
TRIM([remstr FROM] str)
Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.
mysql> SELECT TRIM(' bar ');
+---------------------------------------------------------+
| TRIM(' bar ') |
+---------------------------------------------------------+
| bar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
+---------------------------------------------------------+
| TRIM(LEADING 'x' FROM 'xxxbarxxx') |
+---------------------------------------------------------+
| barxxx |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
+---------------------------------------------------------+
| TRIM(BOTH 'x' FROM 'xxxbarxxx') |
+---------------------------------------------------------+
| bar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
+---------------------------------------------------------+
| TRIM(TRAILING 'xyz' FROM 'barxxyz') |
+---------------------------------------------------------+
| barx |
+---------------------------------------------------------+
1 row in set (0.00 sec)
11. INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.
mysql> SELECT INSTR('foobarbar', 'bar');
+---------------------------------------------------------+
| INSTR('foobarbar', 'bar') |
+---------------------------------------------------------+
| 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
12. LENGTH(str)
Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
mysql> SELECT LENGTH('text');
+---------------------------------------------------------+
| LENGTH('text') |
+---------------------------------------------------------+
| 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
13. LEFT(str,len)
Returns the leftmost len characters from the string str, or NULL if any argument is NULL.
mysql> SELECT LEFT('foobarbar', 5);
+---------------------------------------------------------+
| LEFT('foobarbar', 5) |
+---------------------------------------------------------+
| fooba |
+---------------------------------------------------------+
1 row in set (0.00 sec)
14. RIGHT(str,len)
Returns the rightmost len characters from the string str, or NULL if any argument is NULL.
mysql> SELECT RIGHT('foobarbar', 4);
+---------------------------------------------------------+
| RIGHT('foobarbar', 4) |
+---------------------------------------------------------+
| rbar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
15. MID(str,pos,len)
MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).
NUMERIC FUNCTIONS
The number functions are those functions that accept numeric values and after performing the required operation, returns the numeric values.
1. MOD(N,M)
This function returns the remainder of N divided by M. Consider the following example:
SQL>SELECT MOD(29,3);
+---------------------------------------------------------+
| MOD(29,3) |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
2. POW(X,Y)
POWER(X,Y)
These two functions return the value of X raised to the power of Y.
SQL> SELECT POWER(3,3);
+---------------------------------------------------------+
| POWER(3,3) |
+---------------------------------------------------------+
| 27 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
3. ROUND(X)
ROUND(X,D)
This function returns X rounded to the nearest integer. If a second argument, D, is supplied, then the function returns X rounded to D decimal places. D must be positive or all digits to the right of the decimal point will be removed. Consider the following example:
SQL>SELECT ROUND(5.693893);
+---------------------------------------------------------+
| ROUND(5.693893) |
+---------------------------------------------------------+
| 6 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL>SELECT ROUND(5.693893,2);
+---------------------------------------------------------+
| ROUND(5.693893,2) |
+---------------------------------------------------------+
| 5.69 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
4. SIGN(X)
This function returns the sign of X (negative, zero, or positive) as -1, 0, or 1.
SQL>SELECT SIGN(-4.65);
+---------------------------------------------------------+
| SIGN(-4.65) |
+---------------------------------------------------------+
| -1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL>SELECT SIGN(0);
+---------------------------------------------------------+
| SIGN(0) |
+---------------------------------------------------------+
| 0 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL>SELECT SIGN(4.65);
+---------------------------------------------------------+
| SIGN(4.65) |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
5. SQRT(X)
This function returns the non-negative square root of X. Consider the following example:
SQL>SELECT SQRT(49);
+---------------------------------------------------------+
| SQRT(49) |
+---------------------------------------------------------+
| 7 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
6. TRUNCATE(X,D)
This function is used to return the value of X truncated to D number of decimal places. If D is 0, then the decimal point is removed. If D is negative, then D number of values in the integer part of the value is truncated. Consider the following example:
SQL>SELECT TRUNCATE(7.536432,2);
+---------------------------------------------------------+
| TRUNCATE(7.536432,2) |
+---------------------------------------------------------+
| 7.53 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATE/TIME FUNCTIONS
Date functions operate on values of DATE datatype.
1. CURDATE()
Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
mysql> SELECT CURDATE();
+---------------------------------------------------------+
| CURDATE() |
+---------------------------------------------------------+
| 1997-12-15 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CURDATE() + 0;
+---------------------------------------------------------+
| CURDATE() + 0 |
+---------------------------------------------------------+
| 19971215 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CURRENT_DATE and CURRENT_DATE()
CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE()
2. DATE(expr)
Extracts the date part of the date or datetime expression expr.
mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+
| DATE('2003-12-31 01:02:03') |
+---------------------------------------------------------+
| 2003-12-31 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
3. MONTH(date)
Returns the month for date, in the range 0 to 12.
mysql> SELECT MONTH('1998-02-03')
+---------------------------------------------------------+
| MONTH('1998-02-03') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
4. YEAR(date)
Returns the year for date, in the range 1000 to 9999, or 0 for the .zero. date.
mysql> SELECT YEAR('98-02-03');
+---------------------------------------------------------+
| YEAR('98-02-03') |
+---------------------------------------------------------+
| 1998 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
5. DAY(date)
DAY() is a synonym for DAYOFMONTH().
DAYNAME(date)
Returns the name of the weekday for date.
mysql> SELECT DAYNAME('1998-02-05');
+---------------------------------------------------------+
| DAYNAME('1998-02-05') |
+---------------------------------------------------------+
| Thursday |
+---------------------------------------------------------+
1 row in set (0.00 sec)
6. DAYOFMONTH(date)
Returns the day of the month for date, in the range 0 to 31.
mysql> SELECT DAYOFMONTH('1998-02-03');
+---------------------------------------------------------+
| DAYOFMONTH('1998-02-03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
7. DAYOFWEEK(date)
Returns the weekday index for date (1 = Sunday, 2 = Monday, ., 7 = Saturday). These index values correspond to the ODBC standard.
mysql> SELECT DAYOFWEEK('1998-02-03');
+---------------------------------------------------------+
|DAYOFWEEK('1998-02-03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
8. DAYOFYEAR(date)
Returns the day of the year for date, in the range 1 to 366.
mysql> SELECT DAYOFYEAR('1998-02-03');
+---------------------------------------------------------+
| DAYOFYEAR('1998-02-03') |
+---------------------------------------------------------+
| 34 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
9. NOW()
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.
mysql> SELECT NOW();
+---------------------------------------------------------+
| NOW() |
+---------------------------------------------------------+
| 1997-12-15 23:50:26 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
10. SYSDATE()
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.
mysql> SELECT SYSDATE();
+---------------------------------------------------------+
| SYSDATE() |
+---------------------------------------------------------+
| 2006-04-12 13:47:44 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL Constraints
A constraint is a condition or check applicable on a field or set of fields. The constraints applied to maintain the data integrity are called integrity constraints.
Constraints are used to limit the type of data that can go into a table. Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement). We will focus on the following constraints:
NOT NULL
|
UNIQUE
|
PRIMARY KEY
|
FOREIGN KEY
|
CHECK
|
DEFAULT
|
SQL NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
SQL UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only one primary key.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only one primary key.
SQL PRIMARY KEY Constraint on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:
MySQL:
MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
SQL FOREIGN KEY Constraint
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let's illustrate the foreign key with an example. Look at the following two tables:
The "Persons" table:
Let's illustrate the foreign key with an example. Look at the following two tables:
The "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
The "Orders" table:
O_Id
|
OrderNo
|
P_Id
|
1
|
77895
|
3
|
2
|
44678
|
3
|
3
|
22456
|
2
|
4
|
24562
|
1
|
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables.
The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables.
The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
SQL CHECK Constraint on CREATE TABLE
The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
SQL DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.
The default value will be added to all new records, if no other value is specified.
SQL DEFAULT Constraint on CREATE TABLE
The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
VIEWING A TABLE STRUCTURE
To view the structure or description of the table that you have created the syntax of the command will be
DESC <TABLE NAME>
Or DESCRIBE <TABLE NAME>
CREATING A TABLE FROM AN EXISTING TABLE
We can create a table from a pre existing table with the help of following command
CREATE TABLE orderitem AS
( SELECT ICODE , DESCP
FROM ITEMS
WHERE QOH<ROL
);
INSERTING DATA INTO VALUES
SYNTAX : INSERT INTO <tablename > (<column list>)
VALUES (<value>,<value>)
Eg to insert value in a table employee
INSERT INTO EMPLOYEE(ecode,ename,sex,grade,gross)
VALUES (1001,’RAVI’,’M’,’E4’,6759.70)
Or
INSERT INTO EMPLOYEE VALUES (1001,’RAVI’,’M’,’E4’,6759.70)
INSERTING DATES
Dates are by-default entered in ‘YYYY-MM-DD’
INSERTING NULL VALUES
To INSERT value NULL in a specific column, you can type NULL without quotes and NULL will be inserted in that column.
Consider the following statement
INSERT INTO EMPL (Empno, Ename, Job, Mgr, HireDate,Sal , Comm , Deptno)
VALUES(8100,’yash’,’analyst’,NULL,’10-MAY-03’,6000,NULL,20)
INSERTING DATA FROM ANOTHER TABLE
INSERT INTO BRANCH1
SELECT * FROM BRANCH2
WHERE GROSS> 7000.00
MODIFYING DATA WITH UPDATE COMMAND
Eg If you want to change ROL to 400 only for those items that have ROL as 300, you use the command
UPDATE items
SET ROL = 400
WHERE ROL = 300;
Updating Multiple Columns
UPDATE items
SET ROL = 400, QOH = 700
WHERE icode <’I040’
Using Expressions in Update
UPDATE EMPLOYEE
SET GROSS = GROSS +900
Updating to NULL Values
UPDATE EMPLOYEE
SET GROSS = NULL
WHERE GRADE = ‘E4’
SET GROSS = NULL
WHERE GRADE = ‘E4’
Deleting data with DELETE command
Delete command deletes all the data from the table but keeps the TABLE structure
The syntax for DELETE command is the following :
DELETE FROM ITEMS
The ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
SQL ALTER TABLE Syntax
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype
ADD column_name datatype
To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name
DROP COLUMN column_name
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
ALTER COLUMN column_name datatype
SQL ALTER TABLE Example
Look at the "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to add a column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
We use the following SQL statement:
ALTER TABLE Persons
ADD DateOfBirth date
ADD DateOfBirth date
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.
The "Persons" table will now like this:
The "Persons" table will now like this:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
DateOfBirth
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
| |
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
| |
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Change Data Type Example
Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
We use the following SQL statement:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year
ALTER COLUMN DateOfBirth year
Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two-digit or four-digit format.
Drop TABLE Command
Drop table command deletes all the data and the table structure of the given table . The command for the same is the following
DROP TABLE<tablename>
Nice post . Keep updating Artificial Intelligence Online Training
ReplyDeleteVery very useful...tkns a lot
ReplyDeleteGood post about programming.May ip and cs students would want to become future software engineers.For all engineering and medical entrance examinations there is a coaching institute in Bhubneshwar Orrisa which is coming up in a big way .With faculty members from Patna , Kota, Hyderabad they have been having good results in both engineering and medical entrance examinations and they provide foundation courses as well.Visit http://www.acadejee.com. To see more about the institute check out the link
ReplyDeletehttps://www.facebook.com/100379348337912/videos/589008708389690//