SQL Study Material
SQL Study Material
SQL Study Material
5. What is MySQL?
* It is free and Open Source software which does not require to pay for its usage.
* Itis easy to use, quick and reliable.
* It is platform independent software which works on many operating systems like
Windows, UNIX, LINUX etc,
* It is compatible with many programming languages including JAVA, C++, PHP,
PERL, etc.
* It can handle large amount of data very efficiently and accurately.
8. Creating a Database
Syntax
CREATE DATABASE <database name>;
9. Using a database
Syntax
USE <databasename>;
Syntax
SELECT DATABASE();
Syntax:
SHOW TABLES;
13. Create Table command
Example:
Primary Key
It ensures following when applied:
* Duplicate data is not allowed in the attribute.
* Attribute cannot be left blank
* A relation can have only a primary key
Example:
Foreign Key
Example:
Consider the following tables:
Item (Itemno, Iname, rate)
Orders (Ono, Odate, Itemno, qty, total)
Here in this example Itemno of order table is the foreign key that references
Itemno of Item table which is Primary key.
Unique Key
It ensures following when applied:
* Duplicate data is not allowed in the attribute.
* A relation can have multiple unique keys
Example:
Drop table
Syntax:
DROP TABLE <table name>;
Example
DROP TABLE Orders;
15. How to modify the structure of the table?
Alter table
* It is used to modify the structure of a table. Following are the alterations that
can be done using this command adding a column
* We can add a new column in an existing table.
Example:
Dropping a column
A column can also be modified using Alter Table command as given below:
DML
Insert command
This command is used to insert a tuple in a relation. We must specify the name of the
relation in which tuple is to be inserted and the values. The values must be in the same
order as specified during the Create Table command.
Syntax:
Example:
Delete command
Example:
DELETE FROM item WHERE itemno = 10002’;
Select command
The select command is used to access or retrieve specific or complete set of records
from the database.
Syntax:
SELECT <attribute list> FROM <table name>;
Query 5 : Retrieve item details which rate is less than 100 and qty is more than
100.
DISTINCT Command
Syntax:
SELECT DISTINCT (field that contains repeated data) FROM <table name>
WHERE <criteria>;
Example
SELECT DISTINCT(dept) FROM emplovee:
WHERE clause
* Arithmetic operators
* Relation operators
* Logical operators
* BETWEEN operators
* IN operators
* LIKE operators
Arithmetic operators
Logical operators
BETWEEN operator
© Itis used to define range of values for a particular field in WHERE clause.
© It can be used to define range of numerical or chronological values
© In upper and lower bound given in the range.
IN operator
© It is used to set multiple text comparison for a particular field in WHERE
clause,
© It can only be used for text comparison.
LIKE operator
* It is used to define string expressions (pattern matching) in WHERE clause.
* It uses different wildcards (described in image given below) to define string
expression
Order by clause
* Used to display records in sequential manner in data result set retrieved by
WHERE clause.
* By default displays records in ascending order.
* Keyword ASC (Ascending) or DESC (descending) can be used with order by to
arrange the data result set.
Example
SELECT * FROM item ORDER BY iname;
SELECT * FROM item OREDER BY iname DESC;
Example
Aggregate function
The Group By clause helps to summarize large volume of records. It combines all
those records that have
Identical values in a particular field or a group of fields and produces one summary
record per group.
Having clause
* Helps to filter summarized result produced after grouping.
* It is like post filter which retrieves records from result set produced after
group by query.
* It can include aggregate functions to set the filter criteria
Syntax:
SELECT <field(s)>, <aggregate function(s)>
FROM <table name>
WHERE <condition>
GROUP By <field(s)>
HAVING <expression>
ORDER BY <field(s)> ASC/DESC
Joins
The SQL INNER JOIN command joins two tables based on a common column and
selects rows with matching values in those columns.
Here, the Customers and Orders table are joined on the Customers.customer_id =
Orders.customer condition. The above code excludes all the rows that don't satisfy
this condition.
SELECT columns_from_both_tables
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2
Here,
table1 and table2 are the two tables that are to be joined
column1 is a column in table1 and column2 in a column in table2
Note: We can use JOIN instead of INNER JOIN. Basically, these two clauses
perform the same task.
SQL INNER JOIN
SELECT columns_from_both_tables
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2
Here,
table1 is the left table to be joined
table2 is the right table to be joined
column1 and column2 are the related columns in the two tables
Here, the code left joins the Customers and Orders tables based on customer_id,
which is common to both tables. The result set contains:
customer_id and first_name columns from the Customers table (including
those whose customer_id value is not present in the Orders table)
item column from the Orders table