Search Tutorials

Thursday, 25 April 2013

Tutorial to understand SQL language

First read SQL Statements: DDL, DML and DCL tutorial to understand this tutorial deeply. In this tutorial, we will learn how to create database, tables and how to select/insert/delete/update data in tables.

Creating and Deleting Databases

1) Creating a database
mysql> CREATE database 134a;
Query OK, 1 row affected (0.00 sec)

2) Deleting a database
mysql> DROP database 134a;
Query OK, 0 rows affected (0.00 sec)

Creating a Table

3) After we have created the database we use the USE statement to
change the current database;
mysql> USE 134a;
Database changed

4) Creating a table in the database is achieved with the CREATE table
statement
mysql> CREATE TABLE president (
-> last_name varchar(15) not null,
-> first_name varchar(15) not null,
-> state varchar(2) not null,
-> city varchar(20) not null,
-> birth date
-> death date null
not null default '0000-00-00',
-> );
Query OK, 0 rows affected (0.00 sec)

Examining the Results

5) To see what tables are present in the database use the SHOW tables:
mysql> SHOW tables;
+------------------------+
| Tables_in_134a |
+------------------------+
| president
|
+------------------------+
1 row in set (0.00 sec)

6) The command DESCRIBE can be used to view the structure of a table
mysql> DESCRIBE president;

Inserting / Retrieving Data into / from Tables

7) To insert new rows into an existing table use the INSERT command:
mysql> INSERT INTO president values ('Washington','George','VA','Westmoreland County','17320212','17991214');
Query OK, 1 row affected (0.00 sec)

8) With the SELECT command we can retrieve previously inserted rows:
mysql> SELECT * FROM president;

Selecting Specific Rows and Columns

9) Selecting rows by using the WHERE clause in the SELECT command
mysql> SELECT * FROM president WHERE state="VA";

10) Selecting specific columns by listing their names
mysql> SELECT state, first_name, last_name FROM president;

Deleting and Updating Rows

11) Deleting selected rows from a table using the DELETE command
mysql> DELETE FROM president WHERE first_name="George";
Query OK, 1 row affected (0.00 sec)

12) To modify or update entries in the table use the UPDATE command
mysql> UPDATE president SET state="CA" WHERE first_name="George";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0


Loading a Database from a File

13) Loading a your data from a file into a table.
Assuming we have a file named "president_db" in the current directory,
with multiple INSERT commands in it, we can use the LOAD DATA command to
insert the data into the table president.

mysql> LOAD DATA LOCAL INFILE 'president_db' INTO TABLE president;
Query OK, 45 rows affected (0.01 sec)
Records: 45 Deleted: 0 Skipped: 0 Warnings: 0
Note, that any ascii file that contains a valid sequence of MySql
commands on separate lines can be read in from the command line as:
>mysql -u USERNAME -p < MY_Mysql_FILE


More on SELECT

A general form of SELECT is:
SELECT what to select
FROM table(s)
WHERE condition that the data must satisfy;
Comparison operators are: < ; <= ; = ; != or <> ; >= ; >
Logical operators are: AND ; OR ; NOT
Comparison operator for special value NULL: IS

14) The following MySQL query will return all the fields for the
presidents whose state field is "NY";
mysql> SELECT * FROM president WHERE state="NY";

15) We can limit the values of the returned fields as it is shown bellow:
mysql> SELECT last_name, first_name FROM president WHERE state="NY";

16) The following entry SELECT will return the last name and
birth date of presidents who are still alive
Note: The comparison operator will not work in this case:
mysql> SELECT * FROM president WHERE death = NULL;
Empty set (0.00 sec)
mysql> SELECT last_name, birth FROM president WHERE death is NULL;

17) This command will select the presidents who were born in the
18th century
mysql> SELECT last_name, birth FROM president WHERE birth<"1800-01-01";

18) The following command will select the president who was born first
mysql> SELECT last_name, birth from president ORDER BY birth ASC LIMIT 1;

19) The following query will return the names of fist 5 states (in
descending order) in which the greatest number of presidents have been
born
mysql> SELECT state, count(*) AS times FROM president GROUP BY state
-> ORDER BY times DESC LIMIT 5;

20) The following query will select presidents who have been born
in the last 60 years
mysql> SELECT * FROM president WHERE(YEAR(now())- YEAR(birth)) < 60;
Useful function to retrieve parts of dates are: YEAR(), MONTH(), DAYOFMONTH(),TO_DAY().

21) The following query will sort presidents who have died by their
age and list the first 10 in descending order.
mysql> SELECT last_name, birth, death, FLOOR((TO_DAYS(death) - TO_DAYS(birth))/365) AS age
-> FROM president
-> WHERE death is not NULL ORDER BY age DESC LIMIT 10;

Working with Multiple Tables

22) Often it is useful to separate data in conceptually distinct groups and store them in separate tables. Assuming we have a table that contains students' personal information, and we have another table that contains test scores of students. We can create a common field in each table, say "ssn" and work with the two tables together as follows:

SELECT last_name, address, test_date, score FROM test, student WHERE test.ssn = student.ssn;


Related Tutorials:-

ACID properties of Database

SQL Statements : DDL, DML and DCL

Difference between delete, truncate and drop command in SQL

2 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Very helpful post! I always liked SQL... But I'm studying different discipline at the university, so it's hard for me to combine basic education and study of SQL. Good thing there's one awesome website that helps me in academic writing, so I have more time to learn other things.

    ReplyDelete

Back to Top