What is SQL And also Explained SQL vs MySQL ?

What is SQL?

Relational database creation, deletion, access, and manipulation are all possible with Structured Query Language (SQL). This programming language was created especially for relational database data management.

The standard language for working with relational databases is SQL. All RDBMSs understand this collection of commands, which has evolved into a common language for database administration. SQL or a variant of the language is supported by almost all database servers. Its immense popularity can be attributed to its strength and broad backing.

Basically, SQL lets you construct and maintain a relational database, where all the data is stored in tables. Tables in a relational database are associated to one another based on shared data, defining database relationships. A robust collection of statements for managing, modifying, and safeguarding data kept in a database is offered by SQL.

The main operations that can be performed with SQL include:

• Creating new databases.
• Deleting databases.
• Creating new tables in a database.
• Deleting tables from a database.
• Executing queries against a database to retrieve data.
• Inserting records in a database.
• Updating records in a database.
• Deleting records from a database.
• Creating and removing users (database access control).
• Setting permissions on tables and procedures.
• Creating relationships between tables.

SQL commands fall into a number of general types, including Transaction Control Language (TCL), Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).

• DDL commands specify how data should be stored in the database and define the schema or database structure. CREATE, ALTER, and DROP are a few examples. DDL also owns TRUNCATE and RENAME.
• Data modification and data management within schema objects are handled using DML commands. SELECT, INSERT, DELETE, and UPDATE are a few examples.
• Transactions are managed via TCL commands like COMMIT, ROLLBACK, and SAVEPOINT.
• Permissions and grants are linked to DCL commands, which specify permission to access specific database sections. GRANT and REVOKE are a couple examples.

SQL vs MySQL

MySQL uses SQL, a Standard Query Language. SQL is used to communicate with the database, whereas MySQL administers it and executes SQL queries. The popular ANSI-SQL standards are used in a standard form by MySQL. However, MySQL may not support certain standard SQL capabilities and contains numerous extensions to normal SQL.

SQL was originally called Sequel at IBM’s San Jose Research Laboratory in the 1970s for the System R project. In 1986, ANSI standardized it as SQL.

Here are some basic examples of SQL commands used in MySQL:

Creating a Database: The CREATE DATABASE statement is used to create a new SQL database Syntax:

CREATE DATABASE databasename; 

Example:

CREATE DATABASE sqlcourse;

or

CREATE DATABASE School; 

After creating a database, you can select it to work with using the USE command:

USE databasename; 

Example:

USE School; 

Creating a Table: The CREATE TABLE statement is used to create a new table in a database. It involves specifying the table name and defining columns with their data types and constraints Syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
); 

Example:

CREATE TABLE people (
    id INT NOT NULL,
    email VARCHAR(50) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
); 

Or with additional constraints like PRIMARY KEY and UNIQUE:

CREATE TABLE Student (
    StCode char(3) NOT NULL PRIMARY KEY,
    Stname char(20) NOT NULL,
    StAdd varchar(40),
    AdmNo char(5) UNIQUE,
    StAge integer CHECK (StAge>=5)
); 

Inserting Data: The INSERT INTO statement is used to insert new data into a table Syntax:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); 

Example:

INSERT INTO projects(name, start_date) VALUES('Introduction to SQL Course', '2006-01-01'); 

Retrieving Data (Querying): The SELECT statement is used to extract data from a database. It is a key part of the Data Manipulation Language (DML). The basic form of an SQL query is SELECT [ DISTINCT ] select-list FROM from-list [ WHERE qualification ] Syntax:

SELECT column_name(s) FROM table_name WHERE condition; 

To select all columns, use *:

SELECT * FROM table_name; 

Example retrieving specific columns:

SELECT first_name, last_name, job_title FROM employees; 

Example retrieving based on a condition:

SELECT * FROM projects WHERE start_date='2003-06-01'; 

Using WHERE with BETWEEN operator:

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; 

Example:

SELECT LastName, Salary FROM tblReceptionist WHERE Salary BETWEEN 9000 AND 12000; 

Updating Data: The UPDATE statement is used to modify existing records in a table. It is used with SET and typically a WHERE clause to specify which rows to update Syntax:

UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; 

Deleting Data: The DELETE statement is used to delete records from a table. It is often used with a WHERE clause to specify which records to delete. Without a WHERE clause, it can delete all records Syntax:

DELETE FROM table_name WHERE condition; 

Deleting a Table: The DROP TABLE statement is used to delete a table4. This permanently removes the table and its data Syntax:

DROP TABLE table_name; 

Example:

DROP TABLE Student;

Deleting a Database: The DROP DATABASE statement is used to delete an entire database Syntax:

DROP DATABASE databasename; 

Example:

DROP DATABASE School;

The fundamental purpose of SQL as the language for relational databases is demonstrated by these examples, which show how SQL commands enable users to interact with the database structure and the data stored within it.