SQL Basics Tutorials for Interview preparations: Hands-On Beginner SQL Tutorial

SQL Basics Tutorials for Interview preparations: Hands-On Beginner SQL Tutorial, sql basics pdf, sql tutorial w3schools, Free SQL Tutorial course, sql basics tutorial for beginners, sql basics tutorial pdf, sql basics tutorial for beginners (practice sql queries), sql database basics tutorial. 

• SQL stands for Structured Query Language. It is used for storing and managing data in Relational Database Management System (RDBMS).
• It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables.
• All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard database language.
• SQL allows users to query the database in a number of ways, using English-like statements.
• SQL stands for Structured Query Language. It is used for storing and managing data in Relational Database Management System (RDBMS).
• It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables.
• All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard database language.
• SQL allows users to query the database in a number of ways, using English-like statements.

What are the SQL? SQL follows the following rules:

• Structure query language is not case sensitive. Generally, keywords of SQL are written in uppercase.
• Statements of SQL are dependent on text lines. We can use a single SQL statement on one or multiple text line.
• Using the SQL statements, you can perform most of the actions in a database.
• SQL depends on tuple relational calculus and relational algebra.

What is SQL Process?

• When an SQL command is executing for any RDBMS, then the system figure out the best way to carry out the request and the SQL engine determines that how
• to interpret the task.
• In the process, various components are included. These components can be optimization Engine, Query engine, Query dispatcher, classic, etc.
• All the non-SQL queries are handled by the classic query engine, but SQL query engine won't handle logical files.
• When an SQL command is executing for any RDBMS, then the system figure out the best way to carry out the request and the SQL engine determines that how to interpret the task.
• In the process, various components are included. These components can be optimization Engine, Query engine, Query dispatcher, classic, etc.
• All the non-SQL queries are handled by the classic query engine, but SQL query engine won't handle logical files.

What is Advantages of SQL?

• High speed
• No coding needed
• Well defined standards
• Portability
• Interactive language
• Multiple data view
• High speed
• No coding needed
• Well defined standards
• Portability
• Interactive language
• Multiple data view

What is SQL Datatype?

• SQL Datatype is used to define the values that acolumn can contain. Every column is required to have a name and data type in the database table.

SQL Commands

• SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data. SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.

Types of SQL Commands: There are five types of SQL commands.

1. DDL
2. DML
3. DCL
4. TCL
5. DQL

1) Data Definition Language (DDL)

• DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. All the command of DDL are auto-committed that means it permanently save all the changes in thedatabase.
• Here are some commands that come under DDL:
  • CREATE
  • ALTER
  • DROP
  • TRUNCATE

Data Definition Language (DDL)- CREATE

• CREATE It is used to create a new table in the database.

• REATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

• CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHA R2(100), DOB DATE);

Data Definition Language (DDL)- Drop

• Drop: It is used to delete both the structure and record stored in the table.

• DROP TABLE ;

• DROP TABLE EMPLOYEE;

Data Definition Language (DDL)- ALTER

• ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

• ALTER TABLE table_name ADD column_name COLUMN-definition, ALTER TABLE MODIFY(COLUMN DEFINITION....);

• ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20)); ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

Data Definition Language (DDL)- TRUNCATE

• TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

• TRUNCATE TABLE table_name;

• TRUNCATE TABLE EMPLOYEE;

2) Data Manipulation Language (DML)

• DML commands are used to modify the database. It is responsible for all form of CHANGES in the database.
• The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback.
• Here are some commands that come under DML:
  • INSERT
  • UPDATE
  • DELETE

Data Manipulation Language - INSERT

• INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax:

• INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, .... valueN); OR
• INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);

Example:

• INSERT INTO XYZ (Author, Subject) VALUES ("Sonoo", "DBMS");

Data Manipulation Language - UPDATE

• Update: This command is used to update or modify the value of a column in the table.

Syntax:

• UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

Example:

• UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'

3) Data Control Language

• DCL commands are used to GRANT and TAKE BACK authority from any database user.
• Here are some commands that come under DCL:
  • Grant
  • Revoke

Data Control Language - Grant

• GRANT: It is used to give user access privileges to a database. 
  • Example:
    • GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOT
    • HER_USER;
• REVOKE: It is used to take back permissions from the user. 
  • Example:
    • REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4) Transaction Control Language

• TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.
• Here are some commands that come under TCL:
  • COMMIT
  • ROLLBACK
  • SAVEPOINT

Transaction Control Language - COMMIT

• Commit: Commit command is used to save all the transactions to the database.
• Syntex: COMMIT;
• Example:
  • DELETE FROM CUSTOMERS
  • WHERE AGE = 25;
  • COMMIT;

Transaction Control Language - Rollback

• Rollback: Rollback command is used to undo transactions that have not already been saved to the database. 
• Syntex: ROLLBACK;
• Example:
  • DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK;

Transaction Control Language - Savepoint

• SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction. 
• Syntex:
  • SAVEPOINT SAVEPOINT_NAME;

5) Data Query Language
DQL is used to fetch the data from the database. It uses only one command: SELECT

SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause. 
Syntax:

• SELECT expressions FROM TABLES WHERE conditions;

Example:

• SELECT emp_name FROM employee WHERE age > 20;

SQL Logical Operators
Operator Description
All It compares a value to all values in another value set.
AND It allows the existence of multiple conditions in an SQL statement.
ANY It compares the values in the list according to the condition.
Between It is used to search for values that are within a set of values.
IN It compares a value to that specified list value.
NOT It reverses the meaning of any logical operator.
OR It combines multiple conditions in SQL statements.
EXIST It is used to search for the presence of a row in a specified table.
LIKE It compares a value to similar values using wildcard operator.

Views in SQL
• Views in SQL are considered as a virtual table. A view
also contains rows and columns.
• To create the view, we can select the fields from one
or more tables present in the database.
• A view can either have specific rows based on certain
condition or all the rows of a table.

Creating view
A view can be created using the CREATE VIEW statement. We can
create a view from a single table or multiple tables. 
Syntax
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

Creating View from a single table
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4;