Learn SQL with complete tutorials, SQL query lists, and interactive tools at ByteScout!
Subscribe to our FREE SQL video course!
This tool will help you learn all the necessary basic SQL commands. Test your skills and play!
Explore advanced SQL queries and test your skills with this smart SQL course online.
Check this extensive database of all important queries used in SQL programming. Go back to SQL trainer to practice new skills.
Discover many examples for advanced SQL queries list. This is another level of SQL knowledge.
If you’re learning PostgreSQL, check this list of advanced queries you can use in programming.
Play SQL with a new interactive tool transforming any question to SQL. See the samples.
Explore our rich SQL blog and find extensive articles about:
Check a few detailed comparisons of:
Here are some famous topics:
SQL, structured query language is the most important language which is used to access and manipulate the database. The data is stored in a relational database management system by using various SQL commands and crucial elements. The data can be stored in a tabular format in the RDBMS and can be manipulated by using various SQL elements such as clauses, expressions, predicates, queries. In short, SQL is created for a particular goal: to query data enclosed in a relational database. Following are some of the most important SQL components.
How to create SQL Database?
The data is stored in the database and for that, the database must be created first. The CREATE DATABASE command is utilized to create a new SQL database.
Syntax: CREATE DATABASE DBname;
The following command will create a database Employee.
Example: CREATE DATABASE Employee;
SQL Commands
SQL commands are classified into four categories such as
- DDL: DDL stands for Data Definition Language. It includes commands like Create, Alter, Drop.
- DML: DML stands for Data Manipulation Language. It includes commands like Insert, Updates, Delete.
- DCL: DCL stands for Data Control Language. It includes commands like Grant, Revoke.
- TCL: TCL stands for Transaction Control Language. It includes commands like Commit, Rollback, Savepoint.
How to create a table in MySQL?
The table in MySQL can be created by using a DDL command Create.
Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....);
Example: Following is the table Emp which is created by using Create table command. This table will store information of all the employees.
CREATE TABLE Emp (
Eno int,
Ename varchar(255),
Job varchar(255),
Country varchar(255),
City varchar(255)
);
How to insert data into the table?
The data can be inserted by using insert command. Insert statement is used to insert all the new records into the table.
Syntax: INSERT INTO table_name (column1, column2, column3, ...)
VALUES (val1, val 2, val 3, ...);
Example: The following example is displaying how to insert a single record into the table.
INSERT INTO Emp (Eno, Ename, Job, Country, City VALUES (100, 'Jeff Smith', ‘Clerk', 'Germany', 'Berlin');
If we execute the above command then it will show us that one record is inserted successfully.
How to retrieve data from the table?
The data can be retrieved by using a Select statement. Users can specify the total number of columns one by one or if a user wants to see the complete data then it can be retrieved by using special character ‘*’
Syntax: SELECT column1, column2, ...
FROM table_name;
Example: Now, if you want to retrieve the first two columns from the Emp table then it can be done by using the following command:
Select Eno, Ename from Emp;
The above statement will display data of only the first two columns. If you want to retrieve data of an entire table then it can be done by executing the following command:
Select * from Emp;
How to create SQL Stored Procedures in MySQL?
A stored procedure also called as a subprogram is an important object. A procedure contains a parameter list, and SQL statement/statements. Following is the syntax and example of a procedure. In MySQL, to connect the procedure with an assigned database, users have to define the name as database_name.stored_procedure_name. Here is the complete syntax :
Syntax:
CREATE PROCEDURE procedure_name [ (parameter name[, parameter datatype]) ]
BEGIN
Declaration section
Executable section
END;
There are three types of parameters. IN, OUT and IN OUT. The declaration section is the section where all the variables are declared. The executable section is the section where you write all the executable code.
Example: The following procedure is calculating salary.
CREATE procedure CalcSal ( OUT inc INT )
BEGIN
DECLARE Sal INT;
SET Sal = 100;
label1: WHILE Sal <= 6000 DO
SET Sal = Sal * 3;
END WHILE label1;
SET inc = Sal;
END; //
The above procedure is a stored procedure which can be called anytime by the user.
How to execute a stored procedure in SQL?
The stored procedure can be called by using the following method.
CALL CalcSal (@variablename);
SELECT @variable_name;
How to Create a stored function?
A stored function is also a stored subprogram. The stored functions are normally used to calculate something and they always return a single value. In other words, stored functions are used to pack standard formulas or business rules. Following is the syntax of a stored function.
Syntax:
CREATE FUNCTION function_name(parameter1,parameter2,…)
RETURNS datatype
[NOT] DETERMINISTIC
Statement1;
Statement2;
Example: The following is an example of a function which is calculating the average of four numbers.
DELIMITER |
CREATE FUNCTION CALAVERAGE (a INT, b INT, c INT, d INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE avg INT;
SET avg = (a+b+c+d)/4;
RETURN avg;
END|
How to Execute Function in SQL?
The above function can be executed by using below statement.
Select CALAVERAGE(20,40,60,80);
Output: 50
What are Views in SQL?
In SQL, a view is a virtual table which is the result of an SQL statement. A view comprises of rows and columns, and you can consider views as a virtual image of a table. The entries in a view are columns from multiple tables inside the database.
Syntax:
CREATE VIEW viewname AS
SELECT col1, col2, ...
FROM tablename
WHERE condition;
Example: The following SQL statement creates a view EMPBerlin that displays all employees whose city is Berlin.
CREATE VIEW [EmpBerlin] AS
SELECT Eno, Ename,Job,Sal
FROM Employee
WHERE Country = "Berlin";
Difference Between Views and Tables
In SQL, a table is a collection of data. The data is stored in the form of rows and columns. A view also a collection of data but it is just a SELECT statement which has been stored in the database. You can call it a mirror image of your database or a table.
The benefit of a view is that it can combine data from different tables thus forming a distinct view of it. Suppose you have a database with employees and you require to do any multiple arithmetical queries on it. Instead of addressing the involved query to the database, you can store the query as a view and then by firing a SELECT * FROM view command you can observe all the necessary data.
The great thing about a view is that it always displays the latest data. In other words, a view is just a “saved select statement” that one can reproduce. It’s not literally a table. There are some databases which permit to construct views that have an original table below, it’s actually just a SELECT statement which delivers outcomes.
Important Update
