A beginner’s guide to SQL, Data Definition Language, Data Manipulation Language, Data Control Language, and write SQL Queries & Subqueries.
SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in databases.
SQL was initially developed at IBM in the 1970s, It became a standard of the American National Standards Institute (ANSI) in 1986.
SQL can be used by Database developers to create & manipulate databases, Database Administrators for Database Administration, and Software Testers to conduct Database Testing.
A beginner’s guide to SQL
Introduction to SQL
Introduction to Databases and RDMBS
Install a Database Engine
SQL Syntax
SQL Language Elements
SQL Data Types
SQL Operators
SQL Expressions
SQL – Data Definition Language Commands and Operations.
SQL – Data Manipulation Language Commands and Operations
SQL – Data Control Language Commands and Operations
SQL Functions
SQL Queries and Sub Queries
SQL Clauses
SQL Joins
SQL Views
SQL Indexes
SQL Transactions
SQL Injection
SQL vs. NoSQL
1. Introduction to SQL
1. What is SQL?
• SQL stands for Structured Query Language
• SQL was initially developed at IBM in the 1970s
• SQL is the standard language to communicate with relational database management systems like Oracle, MS Access, MS SQL Server, MySQL, DB2, Sybase Etc…
2. Purpose of SQL
• SQL is used to Create New Databases
• SQL is used to Create New Tables in a Database
• SQL is used to insert records in a Database
• SQL is used to update records in a Database
• SQL is used to Delete records in a Database
• SQL is used to retrieve data from a Database
• SQL is used to execute queries against a Database
• SQL can set permissions on tables, procedures, and views
• SQL is used to create stored procedures in a Database
• SQL is used to create views in a Database
3. Who should learn SQL?
i. Database Developers
Design and deploy database table structures, forms, reports, and queries, etc.
ii. Database Administrators (DBA)
• Keeping databases up to date and managing database access
• Writing Reports, documentation, and operating manuals
iii. Database Testers
• Verify Data Integrity
• Verify Data Manipulations (Add, Update, and Delete)
• Verify Data comparisons
4. What are the subsets of SQL?
SQL Commands can be classified into groups based on their nature, they are,
i. Data Definition Language
ii. Data Manipulation Language
iii. Data Control Language
5. Data Definition Language
Important Commands and Operations in Data Definition Language
i. Create: To create databases and database objects
ii. Alter: To modify existing database objects
iii. Drop: To drop databases and databases objects
iv. Truncate: To remove all records from a table
v. Rename: To rename database objects
6. Data Manipulation Language
Important Commands and Operations in Data Manipulation Language
i. Select: To select specific data from a database
ii. Insert: To insert new records in a table
iii. Update: To update existing records
iv. Delete: To delete existing records from a table
7. Data Control Language
Important Commands and Operations in Data Control Language
i. Grant: To provide access to the Database objects to the users
ii. Revoke: to remove user access rights to the database objects
iii. Deny: To deny permissions to users.
2. Introduction to Databases and RDBMS.
What is a Database?
• A Database is a systematic collection of data.
• Databases support storage and manipulation of data
• Databases make data management easy.
Table and Record
• A Table in a Relational Database is a predefined format of rows and columns that define an entity.
• Each column contains a different type of attribute and each row corresponds to a single record.
• Each Table is provided with a name.
Example: Table name: Students
SID Name Address ContactNo
10001 abcd errtt,ttt 9878787878
10002 xyz fwew,er 8786767676
What is DBMS?
• A Database management System is software designed to assist in maintaining and utilizing a large collection of data.
• The alternative to using a DBMS is to store the data in files and write application-specific code to manage it.
Using a DBMS to manage data has many advantages:
• Data Independence
• Efficient Data Access
• Data Integrity and security
• Data Administration
• Concurrent Access and Data Recovery
etc…
What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields, and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields.
Most RDBMS use SQL as a database query language. The most popular RDBMS are MS SQL Server, DB2, Oracle, and MySQL.
Advantages of Relational Database
1. Simple Model
A Relational Database system is the most simple model, as it does not require any complex structuring or querying processes.
2. Data Accuracy
In the relational database system, there can be multiple tables related to one another with the use of a primary key and foreign key concepts.
3. Easy Access to Data
In the Relational Database System, there is no pattern or pathway for accessing the data, as to another type of databases can be accessed only by navigating through a tree or a hierarchical model.
4. Data Integrity
Data integrity is a crucial characteristic of the Relational Database system. Data integrity aids in making sure of the relational database’s other significant characteristics like Ease of use, precision, and stability of the data.
5. Flexibility
A Relational Database system by itself possesses qualities for leveling up, expanding for bigger lengths, as it is endowed with a bendable structure to accommodate the constantly shifting requirements.
6. Normalization
The methodical style is maintained for making sure of a relational database structure is liberated of any variances that can make a difference in the integrity and accuracy of the tables in the database.
7. High Security
As the data is divided amongst the tables of the relational database system, it is possible to make a few tables to be tagged as confidential and others not.
8. Feasible for Future Modifications
As the relational database system holds records in separate tables based on their categories, it is straightforward to insert, delete or update records that are subjected to the latest requirements.
3. Install a Database Engine
SQL (Structured Query Language) is used to communicate with a database, it is the standard language for relational database management systems.
Database Engine is required to use/practice SQL Commands / Queries, we can use any Database Engine either Oracle or MySQL or MS SQL Server, etc.
MS SQL Server:
MS SQL Server RDBMS is a Commercial Software, but Microsoft providing a Free edition also, MS SQL Server Express Edition is a Free edition of the MS SQL Server database management system.
Download and Install MS SQL Server Express Edition:
MS SQL Server Express Edition
Microsoft SQL Server Express is a version of Microsoft’s SQL Server Relational Database Management System that is free to download, distribute and use. It comprises a database specifically targeted for small-scale applications.
MS SQL Server Express Edition Download Link
After Installation, you can launch SQL Server management studio, launch SQL Query Editor, Writer SQL Commands and Execute.
Note: You can use Oracle Database Engine or MySQL Database Engine for practicing SQL Commands.
4. SQL Syntax
• A database contains one or more tables. Each table is identified by a name, Tables contain records (rows) with data.
• Most of the actions we need to perform on a database are done with SQL statements.
• SQL keywords are NOT case sensitive: select is the same as SELECT
• All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, etc…and all the statements end with a semicolon (;), the semicolon is the standard way to separate SQL Statements.
SQL Comments
Comments are used to explain sections of SQL statements, or to prevent the execution of SQL statements.
a. Single Line Comments
Single line comments start with –.
Example1:
–Select all:
SELECT * FROM Customers;
Example2:
SELECT * FROM Customers — WHERE City=’Berlin’;
b. Multi-line Comments
Multi-line comments start with /* and end with */.
Any text between /* and */ will be ignored.
Example:
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;
5. SQL Language Elements
1. Identifiers:
Names of Database Objects such as Tables, Views, Columns, etc,
2. Data Types:
Define the type of data that is contained by a Column
3. Constants:
Symbols that represent specific data types
4. Operators:
Perform Arithmetic, Comparison, and Logical Operations
5. Functions: Built-in Functions to perform specific operations
6. Clauses:
Constituent components of statements and queries.
7. Expressions:
Produce scalar values, or tables containing columns and rows of data.
8. Queries:
Retrieve the data based on specific criteria, this an important element of SQL.
9. Statements Etc…
SQL Operators
An operator is a reserved word or a character used primarily in an SQL statement’s WHERE clause to perform an operation(s), such as arithmetic, comparisons, and Logical operations.
Operators are used to specifying conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.
Important Categories of SQL Operators are,
1. Arithmetic operators
2. Comparison operators
3. Logical operators Etc…
1. Arithmetic Operators:
Arithmetic operators can perform arithmetical operations on numeric operands involved.
Operator Description
+ Add
– Subtract
* Multiply
/ Division
% Modulus
Examples:
i. Add (+)
SELECT 30 + 20;
ii. Subtract (-)
SELECT 30 – 20;
iii. Multiply (*)
Select 30 * 20;
iv. Division
Select 40 / 20;
v. Modulus (%)
Select 40 % 19;
2. Comparison Operators:
A comparison (or relational) operator is a mathematical symbol that is used to compare between two values.
Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).
The following table describes different types of comparison operators
Operator Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
Examples:
i. Select * From abcd
Where City=’Pune’;
ii. Select * From abcd
Where Age > 25;
iii. Select * From abcd
Where Age >= 25;
iv. Select * From abcd
Where Age < 25;
v. Select * From abcd
Where Age <= 25;
vi. Select * From abcd
Where Age <> 25;
3. Logical Operators:
There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, You can use logical operators in the WHERE clause, which allows you to combine more than one condition.
Operator Description
ALL TRUE if all of the sub-query values meet the condition
AND TRUE if all the conditions separated by AND is TRUE
ANY TRUE if any of the sub-query values meet the condition
BETWEEN TRUE if the operand is within the range of comparisons
EXISTS TRUE if the subquery returns one or more records
IN TRUE if the operand is equal to one of a list of expressions
LIKE TRUE if the operand matches a pattern Try it
NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is TRUE
SOME TRUE if any of the sub-query values meet the condition
Examples:
i. And
Select * From abcd
Where Age > 25 And City =’Hyderabad’;
ii. Or
Select * From abcd
Where Age > 40 Or City =’Pune’;
iii. Between
Select * From abcd
Data Definition Language
SQL (Structured Query Language) is for Database Developers, Database Administrators, and Database testers.
Three main subsets of SQL:
i. Data Definition Language
ii. Data Manipulation Language
iii. Data Control Language
Database Engine is required to practice/use SQL, we can use any Database engine like Oracle, MS SQL Server or MySQL, etc…
Data Definition Language Commands and Operations
Important DDL Commands
1. Create
2. Alter
3. Drop
4. Truncate
5. Rename
Important DDL Operations
1. Create a Database
2. Use Database
3. Rename a Database
4. Drop Database
5. Create a Table
6. Rename Table
7. Add a Column to exiting Table
8. Add multiple columns to existing Table
9. Modify an existing column
10. Rename a Column
11. Drop a Column
12. Truncate a Table
13. Drop a Table
Download and Install MS SQL Server Express Edition (It is Free Edition) and practice SQL Commands and Operations.
1. Create a Database
Syntax:
Create Database databaseName;
Example:
Create Database gcreddyDB;
2. Use Database
Syntax
Use databaseName;
Example:
Use gcreddyDB;
3. Rename a Database
Syntax
Alter Database databaseName Modify Name = newdatabseName;
Example:
Alter Database gcreddyDB Modify Name = hyderabad
Or
Alter Database gcreddyDB
Modify Name = hyderabad
4. Drop a Database
Syntax:
Drop Database databaseName;
Example:
Drop Database gcreddyDB;
5. Create a Table
Syntax:
Create Table tableName
(
column1_name dataType(size),
column2_name dataType(size),
.
.
.
);
Example:
Create Table Students
(
STID int,
STName char(50),
);
View Table info
Select * from Students
View Table Schema
Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘Students’;
6. Rename Table
Syntax:
EXEC sp_rename ‘old_tablename’, ‘new_tablename’;
Example:
EXEC sp_rename ‘Students’, ‘newStudents’;
7. Add a Column to existing Table
Syntax:
Alter Table table_name add column_name dataType(size);
Example:
Alter Table newStudents add City char(50);
8. Add multiple columns to an existing Table
Syntax:
Alter Table table_name add column1_name dataType(size), column2_name dataType(size);
Or
Alter Table table_name add
column1_name dataType(size),
column2_name dataType(size),
.
.;
Example:
Alter Table newStudents add add1 char(100), add2 char(70);
Or
Alter Table newStudents add
add3 char(100),
add4 char(70),
add5 char (100),
phone int;
9. Modify an existing column
Syntax:
Alter Table table_name Alter Column column_name dataType(size);
Example:
Alter Table newStudents Alter Column add1 varchar(150);
10. Rename a Column
Syntax:
EXEC sp_rename ‘table_name.old_column_name’, ‘new_colum_name’;
Example:
ExEC sp_rename ‘newStudents.phone’, ‘mobile’
11. Drop a Column
Syntax:
Alter Table table_name Drop Column column_name;
Example:
Alter Table newStudents Drop Column City;
12. Truncate a Table
Truncate Table command is used to delete complete data from an existing table
Syntax:
Truncate Table table_name;
Example:
Truncate Table newStudents;
13. Drop a Table
Drop Table command is used to delete complete Table (Data and Table Structure) from the Database.
Syntax:
Drop Table table_name;
Example:
Drop Table newStudents;
Data Manipulation Language
Data Manipulation Language (DML) is one of the Subsets of SQL, others are,
i. Data Definition Language
ii. Data Control Language etc…
Database Engine is required to practice/execute SQL Commands…
Database Engine either Oracle or MS SQL Server or MySQL etc…
I installed MS SQL Server Express Edition, It is free Software for Small scale organizations, you can use any other Database Engine also, anyhow Most of the SQL Commands are common for all RDBMS….
Data Manipulation Language commands are used to store, modify, retrieve, and delete data from database tables. In this category we have Select, Insert, Update, Delete Commands….
Important Data manipulation language Commands are,
1. SELECT – Retrieves data from a table
2. INSERT – Inserts data into a table
3. UPDATE – Updates existing data into a table
4. DELETE – Deletes all records from a table
In Order to practice/use SQL DML Commands, first I create a Database, a Table and these are SQL DDL Operations (we have to use DDL Commands)…
Next Insert records into the table, It is DML Operation…
Example:
—Create a Database, table, and insert records…
Create database gcreddy;
Use gcreddy;
Create table abcd(
Id int,
Name varchar (40),
City varchar (30),
);
Insert Into abcd
(Id, Name, City)
values (1, ‘G C Reddy’, ‘Hyderabad’);
Insert Into abcd
(Id, Name, City)
values (2, ‘Mahith’, ‘Nellore’);
Data Manipulation Commands,
1. Select:
The SELECT Statement is used to select data from a database. The result
is stored in a result table, called the result-set.
The SELECT command is the most commonly used command in SQL. It allows database
users to retrieve the specific information they desire from an operational database.
/* Select Syntax
Select colomn1, column2, …
From Table_Name;
Select * From Table_Name; */
Select Id, Name From abcd;
Select * From abcd;
2. Insert:
The INSERT INTO Statement is used to insert new records in a table. We
can insert data to a table in two ways,
Syntax
INSERT INTO table_name (column1, column2 …)
VALUES (value1, value2, …);
Example:
Insert Into abcd (Id, Name, City)
values(4, ‘Vijaya’, ‘Kavali’);
Insert Data Only in Specified Columns
Insert Into abcd (Name)
values (‘Cinnu’);
Note: If you insert a specified column only then the remaining columns are Null…
3. Update:
The UPDATE statement is used to update existing records in a table.
Syntax:
UPDATE table_name
SET column1=value, column2=value2,…
WHERE some_column=some_value
Example:
Update abcd
Set Name = ‘Venkat’, City = ‘Mumbai’
Where Id = 1;
4. Delete:
The SQL DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with the DELETE query to delete selected rows, otherwise, all the records would be deleted.
Syntax: DELETE FROM table_name [WHERE condition];
Example:
Delete From abcd
Where Id = 4;
Delete From abcd;
SQL vs. NoSQL
SQL – Structured Query Language
SQL Databases developed in the 1970s with a focus on reducing data duplication,
Relational databases are still incredibly useful and offering a lot of advantages. Plus, SQL is a very well-developed and admired query language that keeps dominating database systems.
The data storage model in SQL databases is ‘Tables with fixed rows and columns.
Example for SQL or Relational Databases are: Oracle, MySQL, Microsoft SQL Server, and PostgreSQL
NoSQL – Not only SQL
NoSQL databases developed in the late 2000s with a focus on scaling and allowing for rapid application change driven by agile and DevOps practices.
Data storage model in NoSQL databases is Document – JSON documents, Key-value: key-value pairs, Wide-column: tables with rows and dynamic columns, Graph: nodes and edges.
Example for SQL or Relational Databases are:
Document: MongoDB and CouchDB,
Key-value: Redis and DynamoDB,
Wide-column: Cassandra and HBase,
Graph: Neo4j and Amazon Neptune
NoSQL had its flaws. Since each NoSQL database had a different query language, there were a lot more languages to learn. Plus, some additional challenges included extra difficulty connecting databases to applications, and third-party ecosystems.
