More

    Introduction to Structured Query Language (SQL)

    Structured Query Language (SQL) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). 

    A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model. In the relational model of a database, all data is represented in terms of tuples, grouped into relations. Relation, tuple, and attribute are represented as table, row, and column, in a relational database. A relational database can be considered as a container for tables and a table is a grid with rows and columns to hold data. 

    IMPORTANT POINTS ABOUT SQL

    1. SQL lets you communicate with databases to store, manipulate, and retrieve data, and also modify the structure of the database.
    2. Individual statements in SQL are called queries.
    3. SQL is case-INsensitive; however, it is become standard in SQL community to use all capital letters for SQL keywords.
    4. In SQL, extra white spaces like spaces, tabs and carriage returns are stripped out before the SQL statement is processed.
    5. SQL commands can be divided into DDL, DML, DCL and TCL.

    DDL, DML, DCL AND TCL

    SQL commands can be divided into DDL, DML, DCL and TCL:

    • DDL stands for Data Definition Language, and contain commands for creating, modifying and deleting databases, schemas, tables, columns etc. such as CREATE, ALTER, RENAME, DROP etc.
    • DML stands for Data Manipulation Language, and these queries are used to create or manipulate the actual data. SQL implements CRUD operations through INSERT, SELECT, UPDATE and DELETE commands that belong to DML. CRUD (create, read, update and delete) operations are the four basic functions of any persistent storage mechanisms.
    • DCL stands for Data Control Language and contain commands such as GRANT, REVOKE etc.
    • TCL stands for Transaction Control and contain commands such as COMMIT, SAVEPOINT, ROLLBACK etc.

    CREATING TABLES TO TRY OUT THE EXAMPLES

    You need to first install and set up a relational database management system such as MySQL.

    Next you need to create a database and use that database.

    Create database testdb;

    use testdb;

    Data in relational data are stored as tables with rows and columns. So first create 2 tables, employee and emp, with 2 columns empName and id.

    Create table employee(empName varchar(20), id int PRIMARY KEY);

    Create table emp(empName varchar(20), id int PRIMARY KEY);

    You might also have to use some common sense for some of the examples, like deleting/modifying duplicate data.

    CRUD OPERATIONS

    CRUD stands for Create, Read, Update and Delete.

    You create data through INSERT statements, read data through SELECT statements, update data through UPDATE statements and delete data through DELETE statements.

    Data in relational data are stored as tables with rows and columns, and all operations are in general performed row-wise.

    INSERT

    You can use INSERT command to insert data into a table.

    INSERT INTO emp values (‘Heartin’,1);

    We can use INSERT and SELECT together to insert rows from a query. The column names of the columns returned by the SELECT statement are ignored by SQL in an INSERT SELECT statement. So the names of the returned columns do not need to match the column names of the columns data is going into.

    INSERT INTO employee SELECT * from emp;

    SELECT

    You can use SELECT to retrieve data.

    You can specify which columns to retrieve in an SQL SELECT statement or use * for all columns.

    You can also specify an SQL expression, a UNIQUE or DISTINCT, or a combination in a SELECT statement.

    Below are a few different ways in which you can query your data.

    SELECT * FROM employee;

    SELECT empName, id FROM employee;

    SELECT empName, MAX(id) FROM employee;

    SELECT empName, CURRENT_DATE FROM employee;

    SELECT UNIQUE empName FROM Employees;

    SELECT COUNT(DISTINCT lastname) FROM Employees;

    You can use the UNIQUE and DISTINCT keywords as a field constraint, in the WHERE clause or in the SELECT statement.

    • The difference is that UNIQUE treats two NULL rows as still unique, while DISTINCT treats two NULL rows as NOT UNIQUE. 
    • It is better to use DISTINCT as SELECT UNIQUE is considered “old” SQL and your DBMS might not support it.

    UPDATE

    You use the UPDATE statement to change data after it is stored in the table, optionally using WHERE clause. You can also update data using fetched data from a query.

    UPDATE employee set id=id+1000;

    UPDATE employee set id=1001 WHERE id=1;

    DELETE

    You can use DELETE to delete rows from a table.

    DELETE from employee WHERE id=1;

    DATATYPES IN SQL

    Common data types that we can use in SQL are INT, VARCHAR, FLOAT, DATE and TIME

    We can also have user defined data types based on predefined SQL types.

    CONSTRAINTS IN SQL

    Different constraints can be used while creating tables in SQL such as UNIQUE, PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, DEFAULT etc.

    • A PRIMARY KEY is a column or group of columns that represents a unique identifier for each row in a table. PRIMARY key cannot have NULL values.
    • A UNIQUE constraint is similar to a PRIMARY key, but you can have more than one UNIQUE constraint per table. Also, UNIQUE constraints can accept NULL, but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.
    • A FOREIGN KEY in one table points to a PRIMARY KEY in another table; and is used to specify relationship between tables. The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the PK values in the table it points to.
    • The CHECK constraint lets you constrain values stored in your table.
    • DEFAULT constraint gives all fields in a default value.

    COMMONLY ASKED QUESTIONS BASED ON CONCEPTS IN THIS PAGE

    1. What does DDL, DML, DCL and TCL stand for? Give examples for each. 
    2. What is the difference between a Primary Key and Unique constraint?
    3. List down a few constraints in SQL other than Primary Key and Unique?
    4. Differentiate between UNIQUE and DISTINCT?
    5. Is SQL case sensitive?
    6. How does SQL handle whitespaces?

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox