My SQL Revision

 RELATIONAL DATABASE MANAGEMENT SYSTEMS – MySQL Revision 

Data – Raw facts which has a meaning but can’t lead to a conclusion 

Eg – I give you 4 words,  ‘Tom’, 6/6, shades, blue. Tell me what do I want you to lead to?

These 4 words are data (Raw facts definitely has a meaning which every student will think of as his mindset)

When I arrange (Process) these 4 words, add some conjunctions, adjectives will lead you to a conclusion.

Tom with an eyesight 6/6 loves to gear blue shades.

This is information (processed data leading to a conclusion)

Database – is a container/holder (base) which holds data in it.

This document is having data in it so is it a database?

No, a database is an organized structure (not a paragraph) in rows and columns (table) which hold data in it. 

Why? Easy to locate and access specific values within the database.

Like 

AdmNo RollNo Name Percentage

1023 1 Amya 87

5467 15 Navya 99

AdmNo Name DOB

1023 Amya 20/01/2004

5467 Navya 02/05/2005

So, a database is an integrated collection of data records, files, and other objects.

Database Servers are dedicated computers that hold the actual databases and run only the DBMS and related software. 

DBMS - A database management system is a software package with computer programs that controls the creation, maintenance, and use of a database. Database programs such as Microsoft Access, OpenOffice.org Base, and MySQL.

Advantages of Database 

Reduces Data Redundancy – Duplicity of data and file is prevented in a database as there is a single database and any change in it is reflected immediately. 

Sharing of Data -  The data and file can only be shared based with the authorized users. Many remote users can also access the database simultaneously and share the data between themselves.  

Data Integrity – Since data is non repeated and shared efficiently, hence the data is accurate and consistent in the database. 

Data Security – Data and files are shared remotely and only with authorised access which are authenticated using a username and password. 

Data Privacy – Since data are secured so the privacy is also maintained like data are stored at various levels so that only allowed user or owner can access/ view/ manipulate the data.

Backup and Recovery – Data is managed by the dedicated servers so automatic backup is done periodically and hence recovery of the data at a lost is possible. (Now a days cloud storage is available for Database serve) 

Data Consistency - Data consistency is ensured in a database because there is no data redundancy. Data Consistency means there should be multiple mismatching copies of the same data. All data appears consistently across the database and must be same for all the users viewing the database. Moreover, any changes made to the database are immediately reflected to all the users and there is no data inconsistency.

Elements of a Database 

1. Tables - A table is a set of data elements (values) that is organized using a model of vertical columns(which are identified by their name) and horizontal rows.

2. Columns or Fields or Attributes - A column is a set of data values of a particular simple type, one for each row of the table. 

3. Rows or Records or Tuples – A filled in row also called a Record or Tuple represents a single, data item in a table.

Key Features of a Database (Below Figure)

1. A database can have one or many tables.  

2. Each table in a database contains information about one type of item.

3. Record uniqueness.

4. Every database table should have one or more fields designated as key.

Data can be organized into two types: 

1. Flat File - Data is stored in a single table. Usually suitable for less amount of data. DBMS

2. Relational File - Data is stored in multiple tables and the tables are linked using a common field. 

    Relational is suitable for medium to large amount of data. RDBMS

AdmNo RollNo Name Percentage

1023 1 Amya 87

1036 2 Amya 95

5467 15 Navya 99

AdmNo Name DOB

1023 Amya 20/01/2009

1036 Amya 11/02/2009

5467 Navya 02/05/2010

RDBMS – Relational Database Management System 

A relational database management system (RDBMS) is a program / software that allows you to create, update, and administer a database which can have many tables and these tables can be related / linked / connected to each other.

Why? 

So, that a desired result can be obtained which can’t be obtained from one single table.

How?

These tables are created with common columns/fields and this column is defined as a Primary / Foreign Key

Database name – Student_Details (Has 2 tables as below)

Table name1 – Student_Class_Detail        

Columns / Fields / Attributes

AdmNo RollNo Name Percentage

1023 1 Amya 87

1036 2 Amya 95

5467 15 Navya 99

                       Rows  (Filled in rows – Records/Tuples)

The above table has * 4 Columns/Fields namely – AdmNo, RollNo, Name, Percentage  * 5 rows out of which 4 are records/tuples * The first row is the Field headings. * Each Field contains one particular type of data in it. Like RollNo, AdmNo are numeric whereas Name field is alphabetic.

(Alphanumeric data – like address / email id )

Table name2 – Student_Admission_Detail 

AdmNo Name Locality

1023 Amya Vasundhra

1036 Amya Indirapuram

5467 Navya Noida

Now if you are asked to retrieve the percentage of the student whose locality is Noida. 

Then this information can’t be received alone from either of the table.

But the tables are if linked / related to each other, then this information can be retrieved.

So, how to link two tables?

The tables which need to be linked should have a common column in both the tables this column should be set as a primary key. (By default the first column is a primary key)

Primary Key – is a constraint (rule) which when applied to any column of a table gives three powers –

1. Linking - The table can be linked to another table with the same column as primary key of that table.

** Here, there will be one master / parent table and the table to which it is linked will be child table.

2. Unique identification - The column which has been defined as a Primary key will never allow a duplicate value to be stored.

**Uniquely identify a row

3. Not Null - The column will never allow to skip the value or leave the value of that column as blank.

Foreign Key – is a constraint which when applied on the column(s) of a table, then it points to the primary key of another table. They act as a cross-reference between tables.

Now, again if you are asked to retrieve the percentage of the student whose locality is Noida. 

Student_Details (Master Table)                           Student_Admission_Details (Child Table)

AdmNo RollNo Name Gender Percentage

1023 1 Amya M 87.6

1036 2 Amya F 95.9

5467 15 Navya F 99.99

AdmNo Name Locality

1023 Amya Vasundhra

1036 Amya Indirapuram

5467 Navya Noida

         

  

During the creation of both the table AdmNo should be the Primary Key

But when these two tables are linked 

Student_Details is the Parent/ Master table( the table from which the data has to be retrieved) so its column remains as Primary Key

Student_Admission_Details becomes the Child table ( is used for creating link/relation ) so its column is the foreign key.

State the relationship and difference between a primary and foreign key? 

Relationship – They both are column constraints.

Primary Key Foreign Key

1 A primary key is used to ensure data in the specific column is unique.

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.

2 It uniquely identifies a record in the relational database table. It refers to the field in a table which is the primary key of another table.

3 Only one primary key is allowed in a table. Whereas more than one foreign key are allowed in a table.

4 It is a combination of UNIQUE and Not Null constraints. It can contain duplicate values and a table in a relational database.

5 It does not allow NULL values. It can also contain NULL values.

6 Its value cannot be deleted from the parent table. Its value can be deleted from the child table.

  

SQL – Structured Query Language – it is a programming language which allows to create databases and generate queries to get the desired output. But since this is a language so it is based on commands/statements.

(you need to know and learn these commands)

Commands in SQL are of 3(book) /5 categories  –

1. DDL – Data Definition Language – In this section of the language, commands which helps to define / redefine the database / table are used. Like – Create, Alter, Drop, Truncate.

a. Create – CREATE DATABASE database_name ; USE database_name;

      CREATE TABLE table_name ( field1_name field1_type field1_length , field2_name field2_type field2_length , . . . . .)

** First task in SQL is to create a database and then open it (in dbase when you create a database that 

    particular database will be active/open automatically) 

** Second Task in SQL is to Create a table with the structure (field name, type, length, properties)

** Tables can only be created inside a database and nowhere else. (Database – Folder & Tables – Files )

Task in SQL – Create a database named DB1 and create a table named Student_Details as below - 

 AdmNo RollNo Name Percentage

1023 1 Amya 95.00

1036 2 Amya 97.80

5467 15 Navya 99.00

Task 1 - Step 1  CREATE DATABASE DB1;

     OPEN DB1;

  Step 2  CREATE TABLE Student_Details

     ( Admn_No INTEGER(5),

       RollNo INTEGER(3),

       Name VARCHAR(10),

                                 Percentage INTEGER(3)

      );

Step 3  DESC(RIBE) Student_Details;

b. Drop – DROP Database_name;  - will delete the database with all the tables in it.

    Eg - DROP DB1;

      DROP TABLE table_name; - will delete the table with fields and records.

    Eg - DROP TABLE Student_Details;

c. Alter – ALTER TABLE table_name 

    ADD/MODIFY ( Field_name field_type(length)) - will edit the structure of a table. 

Eg -  ALTER  TABLE  STUDENT_DETAILS 

         MODIFY Percentage DECIMAL(6,3);

Eg - ALTER TABLE Student_Details 

        ADD( RANK INTEGER(2));

ALTER  TABLE STUDENT_DETAILS MODIFY (PERCENTAGE DECIMAL(5,2));

Eg - ALTER TABLE Student_Details  MODIFY ( RollNo INTEGER(3) PRIMARY KEY);

d. Truncate – TRUNCATE TABLE table_name; - will delete the records of the table.

 

2. DML – Data Manipulation Language – In this section of the language, commands which helps to manipulate (modify) the records of the table are used. Like – INSERT, UPDATE, DELETE.

SELECT statement is part of DML in some of the DBMS  software. (in Base it is)

a. Insert – will insert / enter a new record in the table which has been created.

INSERT INTO TABLE table_name 

VALUES (value1, value2, value3,……..);

     OR  INSERT INTO TABLE table_name (field1, field2, …..) 

             VALUES (value1, value2, ……..);

     Eg - INSERT INTO TABLE Student_Details VALUES (1023, 1, ‘Amya’, 95);

             INSERT INTO TABLE Student_Details VALUES (1036, 2, ‘Amya’, 97);

INSERT INTO TABLE Student_Details VALUES (5467, 15, ‘Navya’, 99);

**One record entry at a time

     OR

INSERT INTO TABLE Student_Details (AdmNo, RollNo, Name, Percentage) 

             VALUES (1023, 1, ‘Amya’, 95) (1036, 2, ‘Amya’, 97) (5467, 15, ‘Navya’, 99);

** All the records are entered in one go

** When the records are entered using INSERT statement the field values of type char, varchar and date are always enclosed within single / double quotes.

b. Update – statement will modify the records of the table (Cell values can be changed)          

          UPDATE table_name SET [field1= value1,... fieldN = valueN] 

          WHERE [condition];

Eg -   UPDATE Student_Details SET Percentage = 100 

          WHERE RollNo = 2;

c. Delete – statement will delete record(s) from the table.

         DELETE FROM table_name 

         WHERE [condition];

Eg -  DELETE FROM Student_Details 

         WHERE RollNo > 10;

3. DCL (Data Control Language) – These commands / statements give access to the users to the database. Like - GRANT AND REVOKE.

a. Grant – statement is used to give user(s) access privileges/permissions to a database.

GRANT [Statement1, Statement2….] 

ON table_name TO [user1, user2,….];  

Eg  -      GRANT INSERT, UPDATE 

              ON Student_Details TO Ansh, Vansh;  

b. Revoke – statement is used to take back access privileges from a user over a database.

REVOKE [Statement1, Statement2,…] 

ON table_name 

FROM [user1, user2,….];  

Eg  -      REVOKE UPDATE 

              ON Student_Details 

              FROM Ansh;  

Extras 

4. TCL (Transaction Control Language) – Transactions are the statements executed over a table one after the other. Like – Create Table ………………… ;

          Insert Into …………………...;

          Update ………………………;

          Delete ………….…………….;

So the collection of these consecutive statements executed over one table is called a transaction. And also these statements make the changes in the table permanently (Saved).

Now, TCL statements helps to save, undo the statements given to a table like INSERT, UPDATE, DELETE or undo up to a particular statement in a Transaction. Like – Commit, Rollback, Savepoint.

Extras – 

a. Commit – statement is used to Save a command / statement given to a table. By default the commands are all saved automatically in SQL.

         COMMIT;

Eg -  DELETE FROM Student_Details 

         WHERE RollNo=15;  

         COMMIT;  

b. Rollback – statement is used to undo the immediate previous statement in a transaction.

         ROLLBACK;

Eg -  DELETE FROM Student_Details 

         WHERE RollNo=1;

         ROLLBACK;

 c. Savepoint – statement is used to create a bookmark up to a particular statement in a transaction. There can be 

     more than one bookmark in a transaction.

        SAVEPOINT savepoint_name;

  Eg – Create Table table_name …………. ;

           Insert Into table_name ……………..;

           SAVEPOINT Mark1;

           Update ……………… ;

           SAVEPOINT Mark2;

           Alter Table ………… ;

           ROLLBACK TO Mark2;

           COMMIT TO Mark1;

Extras End Here

5. DQL (Data Query Language) – helps to extract/retrieve/fetch  all or some of the records from the specified column(s) of the table. It has only one statement - SELECT. 

SELECT statement is like a print statement which is used to show all or selective records.

Query – is a statement generated on a table or paired table to get / extract / filter the desired information.

** Every statement / Query in SQL must end with a semi-colon ( ; ) which indicates the end of a statement. Which means the statement/query is ready for execution. 

Query 1          

SELECT   */ALL   FROM table_name;

** ALL – means all the columns (with all the values) will be displayed,   * is also used in place of ALL.

Eg1 – SELECT *  FROM Student_Details;

OR     SELECT ALL  FROM Student_Details;

 AdmNo RollNo Name Percentage

1023 1 Amya 95

1036 2 Amya 97

5467 15 Navya 99

o/p  

Query 2   SELECT field_name(s)    

                   FROM table_name;

Eg 2 -  SELECT RollNo, Name

            FROM Student_Details;

O/p  

RollNo Name

 1 Amya

 2 Amya

15 Navya

       

Query 3  SELECT field_name(s)    

                   FROM table_name

       WHERE [condition];

Eg 3 -  SELECT RollNo, Name, Percentage

            FROM Student_Details

            WHERE Percentage>95;

O/p  

RollNo Name Percentage

 2 Amya 97

15 Navya 99

** WHERE is the optional clause which is used to filter or extract selective records from the table by specifying conditions over the columns. WHERE clause helps in Grouping of Data of a particular type.

 AdmNo RollNo Name Percentage

1023 1 Amya 95

1036 2 Amya 97

5467 15 Navya 99

Query 4  SELECT field_name(s)    

                         FROM table_name

             [ORDER BY field_name];

Eg 4 - SELECT  * FROM Student_Details

           ORDER BY Name DESC;

 AdmNo RollNo Name Percentage

1023 1 Amya 95

1036 2 Amya 97

5467 15 Navya 99

O/p -

 AdmNo RollNo Name Percentage

1036 2 Amya 97

1023 1 Amya 95

5467 15 Navya 99

ORDER BY clause is used to sort the table on a particular column. By default the ORDER BY clause sorts the mentioned column in ascending order. But to sort the column in descending order DESC keyword can be used.

Here, output is same as the original table. Because the table is already sorted in ascending order for each field.

ORDER BY clause helps in Grouping of Data of a particular type.

Eg 5 - SELECT  * FROM Student_Details;

           ORDER BY Name DESC;

o/p - 

 AdmNo RollNo Name Percentage

5467 15 Navya 99

1036 2 Amya 97

1023 1 Amya 95

  

In the above output the Table has been sorted on the Name field in the descending order. But when two or more cells have the same value of the sorting order filter then the sorting is done on the primary key field (ascending)

Performing Basic Mathematic Calculations

1. ‘+’ operator – for adding a value to a field (all the cells of that column)

2. ‘-‘ operator – for subtracting a value from a field 

3. ‘*’ operator – for multiplying a value to a field

4. ‘/’ operator – for getting the quotient by dividing a value from a field   11/2 = Q=5   R=1

EmpCode EmpName EmpSalary

1001 Ranvir Pratap 50000

1023 Meghashree Dutta 70000

1120 Shreshkar Shroff 45000

Emp table –

Eg 1 – SELECT EmpName, EmpSalary+10000 FROM Emp;

EmpName EmpSalary

Ranvir Pratap 60000

Meghashree Dutta 80000

Shreshkar Shroff 55000

o/p -      

            

** These operations are temporary, will not edit and save the records in the table (because it is being done with the SELECT statement which is only due for showing the output not for saving the statements)

Eg 2 - SELECT EmpName, EmpSalary-5000 FROM Emp;

EmpName EmpSalary

Ranvir Pratap 45000

Meghashree Dutta 65000

Shreshkar Shroff 40000

o/p - 

Eg 3 - SELECT EmpName, EmpSalary*2 FROM Emp;

EmpName EmpSalary

Ranvir Pratap 100000

Meghashree Dutta 140000

Shreshkar Shroff   90000

o/p -  

Eg 4 - SELECT EmpName, EmpSalary/10 FROM Emp;

EmpName EmpSalary

Ranvir Pratap 5000

Meghashree Dutta 7000

Shreshkar Shroff 4500

o/p - 

1. The types of languages used for creating and manipulating the data in the Database are  DDL & DML.

2. A DDL is a standard for commands that define the different structures in a database. 

3. A DML is a language that enables users to access and manipulate data in a database. 

4. A Query is a part of DML involving information retrieval only. 

5. A popular data manipulation language is SQL (Structured Query Language).

Fill in the blanks 

1. A DBMS software helps the user to systematically store information in the databas 

2. SELECT statement retrieves zero or more rows from one or more database tables or database views. 

3. By default, data is arranged in Ascending order using ORDER BY clause. 

4. UPDATE statement is used for modifying records in a database. 

5. DELETE statement is used to remove one or more records in a Database. 

Short Answer Questions: 

1. Name DML commands. 

   Ans.   INSERT, UPDATE, DELETE, SELECT

2. What is the purpose of using queries? 

    Ans.   To retrieve/extract/fetch/filter all or some of the columns and its selective records.

3. Which clause of Select statement helps to display specific data?

    Ans.   WHERE clause. 

4. Differentiate between Where and Order by clause of SQL statements. 

   WHERE      ORDER BY

* is used to retrieve selective records from the table   |   is used to sort a table on the specified column

* needs a condition to show the selective record         |   does the sorting in ascending order otherwise DESC 

   which uses relational operators like < , > , = , NOT     keyword needs to be used for sorting the column        

in descending order.

Eg – SELECT * FROM table_name SELECT * FROM table_name

         WHERE field_name >/</=/ IS NOT field_value;         ORDER BY field_name DESC;

5. State the purpose of Update Command with the help of an example. 

Update statement is used to change or modify a particular column value (Cell Value) of a table.

Like, we have a table named – Library as below 

  BookCode BookName Author BookPrice

111010 Fear Not Be Strong Swami Vivekanand 500.00

111100 The Lost Symbols Dan Brown 300.00

Now, if the price of the book The Lost Symbols has to be changed to 400.00 permanently then we will

Write the statement as below 

UPDATE Library SET BookPrice = 400.00 

WHERE BookName = ‘The Lost Symbols’ ;

O/p -  SELECT * FROM Library;

  BookCode BookName Author BookPrice

111010 Fear Not Be Strong Swami Vivekanand 500.00

111100 The Lost Symbols Dan Brown 400.00

****************************************