Question

ON SQL

Explain Normalization and Denormalization of data, What is ACID in SQL? Please explain in your own word as much as possible, List the steps in creating a View, List the steps in modifying a View, List the steps in creating a table, List the steps in modifying a table, What are the different Joins? please give examples, Create a database and populate the database with data of your choice, List and explain the different data types used in a table, Explain Primary Key, Foreign Key, and Unique Key.

Answer 1

Normalization:

Normalisation is used to reduce rendundancy in the data of tables in database

It avoids insert,update and delete anomolies which occurs due to redundant data.

Denormalization:

Denormalization is a database optimization technique in which we add redundant data the tables of database. This can help us to avoid costly joins in a relational database.

This can be done after normalization.

ACID properties:

A - Atomicity:

Atomicity is nothing but we have to do the entire transaction at at time or not at all.It follows all or nothing rule.

There is no chance for partial transactions.

Abort : If we abort the transaction,changes made are not visible.

Commit : If we commit the transaction,changes made are visible.

C-Consistency:

It means the database has to maintain the integrity constraints before and after the transaction.There should be no change in the constraints due to transaction.

I - Isolation:

Isolation property ensures multiple transactions can occur concurrently without leading to the inconsistency of database state. Transactions occur independently without interference. Changes occurring in a one transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed.

D-Durability:

Durability is the property of assuring the data of the database will never lost.Once the transaction has completed,the updates will be stored in a permanent disk so that the data will be never losteven in the case of system failure.

Database creation and inserting data:

Primary key:

It is the attribute which uniquely identifies each ane every record of the relation.

it doesn't accepts null values.

Foreign key:

It is the attribute which refers the attribute of another table.

Unique key:

It is the attribute which uniquely identifies each ane every record of the relation.

it accepts null values.

Creation of database:

mysql> create database joins;
Query OK, 0 rows affected (0.16 sec)

mysql> use joins;
Database changed

creation of tables:

mysql> create table Student(SID varchar(10),SName char(20),SAge int,CID varchar(10));
Query OK, 0 rows affected (0.95 sec)

mysql> create table course(CID varchar(10),CName char(20),CFee float);
Query OK, 0 rows affected (0.47 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| SID   | varchar(10) | YES |     | NULL    |       |
| SName | char(20)    | YES |     | NULL    |       |
| SAge | int(11)     | YES |     | NULL    |       |
| CID   | varchar(10) | YES |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| CID   | varchar(10) | YES |     | NULL    |       |
| CName | char(20)    | YES |     | NULL    |       |
| CFee | float       | YES |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Inserting data:

mysql> insert into Student values("S160144","Praveena",21,"c1");
Query OK, 1 row affected (0.09 sec)

mysql> insert into Student values("S160431","Kalyani",22,"c2");
Query OK, 1 row affected (0.13 sec)

mysql> insert into Student values("S160646","Anusha",24,"c4");
Query OK, 1 row affected (0.06 sec)

mysql> insert into Student values("S160619","Uma Maheswari",23,"c3");
Query OK, 1 row affected (0.16 sec)

mysql> insert into Course values("c1","OOPS",50000);
Query OK, 1 row affected (0.05 sec)

mysql> insert into Course values("c2","PYTHON",40000);
Query OK, 1 row affected (0.10 sec)

mysql> insert into Course values("c5","C",60000);
Query OK, 1 row affected (0.07 sec)

mysql> select * from Student;
+---------+---------------+------+------+
| SID     | SName         | SAge | CID |
+---------+---------------+------+------+
| S160144 | Praveena      |   21 | c1   |
| S160431 | Kalyani       |   22 | c2   |
| S160646 | Anusha        |   24 | c4   |
| S160619 | Uma Maheswari |   23 | c3   |
+---------+---------------+------+------+
4 rows in set (0.00 sec)

mysql> select * from Course;
+------+--------+-------+
| CID | CName | CFee |
+------+--------+-------+
| c1   | OOPS   | 50000 |
| c2   | PYTHON | 40000 |
| c5   | C      | 60000 |
+------+--------+-------+
3 rows in set (0.00 sec)

Join queries:

//Query 1:Write a query for inner join.

mysql> select * from Student inner join course on Student.CID=Course.CID;
+---------+----------+------+------+------+--------+-------+
| SID     | SName    | SAge | CID | CID | CName | CFee |
+---------+----------+------+------+------+--------+-------+
| S160144 | Praveena |   21 | c1   | c1   | OOPS   | 50000 |
| S160431 | Kalyani |   22 | c2   | c2   | PYTHON | 40000 |
+---------+----------+------+------+------+--------+-------+
2 rows in set (0.00 sec)

//Query 2:Write a query for left outer join.

mysql> select * from Student left Outer join course on Student.CID=Course.CID;
+---------+---------------+------+------+------+--------+-------+
| SID     | SName         | SAge | CID | CID | CName | CFee |
+---------+---------------+------+------+------+--------+-------+
| S160144 | Praveena      |   21 | c1   | c1   | OOPS   | 50000 |
| S160431 | Kalyani       |   22 | c2   | c2   | PYTHON | 40000 |
| S160646 | Anusha        |   24 | c4   | NULL | NULL   | NULL |
| S160619 | Uma Maheswari |   23 | c3   | NULL | NULL   | NULL |
+---------+---------------+------+------+------+--------+-------+
4 rows in set (0.00 sec)

//Query 3:Write a query for right outer join.

mysql> select * from Student right Outer join course on Student.CID=Course.CID;
+---------+----------+------+------+------+--------+-------+
| SID     | SName    | SAge | CID | CID | CName | CFee |
+---------+----------+------+------+------+--------+-------+
| S160144 | Praveena |   21 | c1   | c1   | OOPS   | 50000 |
| S160431 | Kalyani |   22 | c2   | c2   | PYTHON | 40000 |
| NULL    | NULL     | NULL | NULL | c5   | C      | 60000 |
+---------+----------+------+------+------+--------+-------+
3 rows in set (0.00 sec)

//Query 4:Write a query for full outer join.

mysql> select * from Student right Outer join course on Student.CID=Course.CID union select * from Student left Outer join course on Student.CID=Course.CID;
+---------+---------------+------+------+------+--------+-------+
| SID     | SName         | SAge | CID | CID | CName | CFee |
+---------+---------------+------+------+------+--------+-------+
| S160144 | Praveena      |   21 | c1   | c1   | OOPS   | 50000 |
| S160431 | Kalyani       |   22 | c2   | c2   | PYTHON | 40000 |
| NULL    | NULL          | NULL | NULL | c5   | C      | 60000 |
| S160646 | Anusha        |   24 | c4   | NULL | NULL   | NULL |
| S160619 | Uma Maheswari |   23 | c3   | NULL | NULL   | NULL |
+---------+---------------+------+------+------+--------+-------+
5 rows in set (0.00 sec)

//Query 5:Write a query for cross join.

mysql> select * from Student cross join Course;
+---------+---------------+------+------+------+--------+-------+
| SID     | SName         | SAge | CID | CID | CName | CFee |
+---------+---------------+------+------+------+--------+-------+
| S160144 | Praveena      |   21 | c1   | c1   | OOPS   | 50000 |
| S160144 | Praveena      |   21 | c1   | c2   | PYTHON | 40000 |
| S160144 | Praveena      |   21 | c1   | c5   | C      | 60000 |
| S160431 | Kalyani       |   22 | c2   | c1   | OOPS   | 50000 |
| S160431 | Kalyani       |   22 | c2   | c2   | PYTHON | 40000 |
| S160431 | Kalyani       |   22 | c2   | c5   | C      | 60000 |
| S160646 | Anusha        |   24 | c4   | c1   | OOPS   | 50000 |
| S160646 | Anusha        |   24 | c4   | c2   | PYTHON | 40000 |
| S160646 | Anusha        |   24 | c4   | c5   | C      | 60000 |
| S160619 | Uma Maheswari |   23 | c3   | c1   | OOPS   | 50000 |
| S160619 | Uma Maheswari |   23 | c3   | c2   | PYTHON | 40000 |
| S160619 | Uma Maheswari |   23 | c3   | c5   | C      | 60000 |
+---------+---------------+------+------+------+--------+-------+
12 rows in set (0.00 sec)

//Query 6:Write a query for Natural join.

mysql> select * from Student Natural join Course;
+------+---------+----------+------+--------+-------+
| CID | SID     | SName    | SAge | CName | CFee |
+------+---------+----------+------+--------+-------+
| c1   | S160144 | Praveena |   21 | OOPS   | 50000 |
| c2   | S160431 | Kalyani |   22 | PYTHON | 40000 |
+------+---------+----------+------+--------+-------+
2 rows in set (0.00 sec)

//Query 7:Write a query for equi join.

mysql> select * from Student,Course where Course.CID=Student.CID;
+---------+----------+------+------+------+--------+-------+
| SID     | SName    | SAge | CID | CID | CName | CFee |
+---------+----------+------+------+------+--------+-------+
| S160144 | Praveena |   21 | c1   | c1   | OOPS   | 50000 |
| S160431 | Kalyani |   22 | c2   | c2   | PYTHON | 40000 |
+---------+----------+------+------+------+--------+-------+
2 rows in set (0.00 sec)

//Query 8:Write a query for Condition join.

mysql> select * from Student,Course where Course.CID<>Student.CID;
+---------+---------------+------+------+------+--------+-------+
| SID     | SName         | SAge | CID | CID | CName | CFee |
+---------+---------------+------+------+------+--------+-------+
| S160144 | Praveena      |   21 | c1   | c2   | PYTHON | 40000 |
| S160144 | Praveena      |   21 | c1   | c5   | C      | 60000 |
| S160431 | Kalyani       |   22 | c2   | c1   | OOPS   | 50000 |
| S160431 | Kalyani       |   22 | c2   | c5   | C      | 60000 |
| S160646 | Anusha        |   24 | c4   | c1   | OOPS   | 50000 |
| S160646 | Anusha        |   24 | c4   | c2   | PYTHON | 40000 |
| S160646 | Anusha        |   24 | c4   | c5   | C      | 60000 |
| S160619 | Uma Maheswari |   23 | c3   | c1   | OOPS   | 50000 |
| S160619 | Uma Maheswari |   23 | c3   | c2   | PYTHON | 40000 |
| S160619 | Uma Maheswari |   23 | c3   | c5   | C      | 60000 |
+---------+---------------+------+------+------+--------+-------+
10 rows in set (0.00 sec)

//Query 9:Write a query on Self join.

mysql> select C1.* from Course C1,Course C2 where C1.CFee=C2.CFee and C1.CName="OOPS";
+------+-------+-------+
| CID | CName | CFee |
+------+-------+-------+
| c1   | OOPS | 50000 |
+------+-------+-------+
1 row in set (0.00 sec)