STRUCTURE QUERY LANGAUGE

STRUCTURE QUERY LANGUAGE

Introduction to SQL

Structured Query Language (SQL) is the most popular query language used by major relational database management systems such as MySQL, ORACLE, SQL Server, etc. SQL is easy to learn as the statements comprise of descriptive english words and are not case sensitive.

SQL provides statements for defining the structure of the data, manipulating data in the database, declaring constraints and retrieving data from the database in various ways, depending on your requirements.

SQL provides variety of tasks such as

Querying data.
Creating, replacing, altering and dropping tables.
Inserting, updating and deleting rows in a table.
Controlling access to the database.
Guaranteeing database consistency and integrity

Advantage & Disadvantage of MY SQL

Advantages of MySQL:

1. Open Source and Free:- MySQL is open source, meaning it’s free to use for most applications, which makes it accessible to individuals, small businesses, and large enterprises alike. There are also paid versions with additional features for enterprise use.

2. Easy to Use:- MySQL is relatively easy to install, configure, and use, making it a good choice for beginners. Its syntax is simple and intuitive compared to other databases.

3. Cross-Platform Support:- MySQL supports multiple operating systems, including Windows, Linux, and macOS, providing flexibility in deployment environments.

4. High Performance:- MySQL is optimized for read-heavy operations and offers excellent performance, particularly for web applications with high query rates, such as content management systems (CMS) and e-commerce platforms.

5. Security:- MySQL includes robust security features like user authentication, SSL support for encrypted connections, and granular access controls, making it secure for managing sensitive data.

6. Integration:- MySQL is compatible with many programming languages like PHP, Python, Java, and .NET. It also works well with other open-source technologies and is the database of choice for many web applications, including those built on the LAMP (Linux, Apache, MySQL, PHP) stack.

Disadvantages of MySQL:

1. Limited for Complex Queries:- While MySQL works well for most general queries, it can struggle with complex and resource-intensive queries (e.g., large joins, subqueries), especially as databases grow larger.

2. Lack of Full SQL Compliance:- MySQL does not fully comply with the SQL standard in certain areas. For example, certain advanced features of SQL, such as some types of triggers, foreign key constraints, and recursive queries, may have limitations compared to other databases like PostgreSQL.

3. Performance Limitations with Write-Intensive Applications:- MySQL is optimized for read-heavy workloads, but it can struggle with performance in write-intensive applications, such as high-concurrency systems that require frequent updates.

SQL STATEMENTS

SQL command or statement is a special kind of sentence that contains clauses and all end with Semicolon;

DDL(Data Definition Language) :

It provides statements for creation and deletion of the database tables, views, etc.
The DDL provides a set of definitions to specify the storage structure in a database system. Some DDL statements are as follows

CREATE used to create new table in the database.
DROP used to delete tables from the database.
ALTER used to change the structure of the database table. This statement can add up additional column, drop existing, and even change the data type of columns involved in a database table.
RENAME used to rename a table.

DML(Data Manipulation Language):

It provides statements for manipulating the database objects. It is used to query the databases for information retrieval.

Some DML statements are as follows

INSERT used to insert data into a table.
SELECT used to retrieve data from a database.
UPDATE used to update existing data within a table.
DELETE used to delete all records from a table.

DCL(Data Control Language) :

It is used to assign security levels in database, which involves multiple user setups. They are used to grant defined role and access privileges to the users.

Some DCL statements are as follows

GRANT used to give user’s access privileges to database.
REVOKE used to withdraw access

TCL (Transaction Control Language)

It is used for controlling the transactions in a database system. These are also used to manage the changes made by DML.

Some TCL statements are as follows

COMMIT used to save the work done.
SAVEPOINT used to identify a point in a transaction to which you can later rollback.
ROLLBACK used to restore database to original since the last COMMIT.
SET TRANSACTION establishes properties for the current transactions.

Rules for SQL commands

Rules for SQL commands are given below.

SQL statements can be typed in lowercase or uppercase letter. SQL statements are not case sensitive.
The statements can be typed in single line or multiple lines.
A semicolon (;) is used to terminate the SQL statements.
The statements may be distributed across the line but keywords cannot be.
A comma (,) is used to separate parameters without a clause.
Characters and date constants or literals must be enclosed in single quotes (‘A’).
A command can be typed either full or first four characters.

SQL DATA TYPES

Data Type	Syntax	Explanation
1.INTEGER	INTEGER or INT	A 32-bit signed integer value and its range from -2147483648 to 2147483647. .
2.SMALLINT	SMALL INT	A 16-bit signed integer value and its range from -32768 to 32767.
3. NUMERIC	NUMERIC(p,s)	Where, p is a precision value and s is a scale value. e.g. numeric (6,2) is a 6 digit number that has 4 digit before the decimal and 2 digit after the decimal.
4.DECIMAL	DECIMAL(p,s)	Where, p is a precision value and s is a scale value. (same as NUMERIC) .
5.REAL	REAL	Single-precision floating point number.
6. DOUBLE PRECISION	DOUBLE PRECISION	Double-precision floating point number.
7.FLOAT	FLOAT(p)	Where, p is a precision value.
8.CHARACTER	CHAR(x)	Where, x is the number of characters to be stored. This data type will occupy space for NULL values. It can hold atmost 255 characters.
8.CHARACTER VARYING	VARCHAR(x)	Where, x is the number of characters to be stored. It will occupy space for NULL values. It can hold atmost 2000 characters and used in ANSI standard.
9.DATE	DATE	Stores year, month and day values.
10.TIME	TIME	Stores hour,minute and second values

SQL Command Basic

SQL database is a way of organizing a group of table and table stores the data in the form of rows and columns

CREATING AND USING A DATABASE :

CREATE Database command will create an empty database with the specified name and would not contain any table
For creating a database we have to type CREATE then database name command.
SYNTAX :- CREATE DATABASE DATABASE_NAME
EXAMPLE :- CREATE DATABASE SCHOOL;

HOW TO SELECT DATABASE :

Once you create a database you have to access those database by ‘USE’ Keyword.
SYNTAX :- USE DATABASE_NAME;
EXAMPLE :- USE STUDENT;

SHOW DATABASES :

To check the names of the existing databases on the server you need to use the SHOW command.
SYNTAX :- SHOW DATABASES;
EXAMPLE :- SHOW DATABASES;

DROPPING a DATABASE

Database can be removed or deleted using DROP command.
SYNTAX :- DROP DATABASE <database_name>;
EXAMPLE :- DROP DATABASE SCHOOL;

DDL STATEMENTS / COMMANDS

CREATING STATEMENT :

The CREATE statement is used to create a table in a database. We need to give information about the table data by given proper columns and row with their constraint.
SYNTAX :- CREATE TABLE <TABLE_NAME>(<column_name ><data_type>[(<size>)][constraints],
<column_name ><data_type>[(<size>)][constraints],
<column_name ><data_type>[(<size>)][constraints]
);
EXAMPLE :-
Create table Student
(
No int primary key,
Name varchar(50),
age int,
department varchar(50),
DOA date,
fee int,
gender varchar(6)
);

CONSTRAINTS

Constraints are rules enforced on data columns to ensure the integrity, accuracy, and reliability of the data stored in the database. They are used to limit the type of data that can be inserted into a table, ensuring that the database remains accurate and consistent.

Constraint	Description
1.NOT NULL	Ensures a column cannot have a NULL value. CREATE TABLE users ( id INT NOT NULL, name VARCHAR(50) NOT NULL );
2.UNIQUE	Ensures all values in a column are unique. CREATE TABLE users ( id INT NOT NULL, email VARCHAR(100) UNIQUE );
3.PRIMARY KEY	A unique identifier for a record that cannot be NULL. Combines `NOT NULL` and `UNIQUE` CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(100) UNIQUE );
4.FOREIGN KEY	Ensures referential integrity by linking two tables. CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) );
5.CHECK	Ensures that all values in a column meet a specific condition. CREATE TABLE employees ( id INT PRIMARY KEY, age INT CHECK (age >= 18) );
6.DEFAULT	Sets a default value for a column if no value is provided. CREATE TABLE products ( id INT PRIMARY KEY, price DECIMAL(10,2) DEFAULT 0.00 );
7.AUTO_INCREMENT	Automatically generates a unique value for a column. CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL );

DROPPING a DATABASE

Database can be removed or deleted using DROP command.
SYNTAX :- DROP DATABASE <database_name>;
EXAMPLE :- DROP DATABASE SCHOOL;

DROPPING a DATABASE

Database can be removed or deleted using DROP command.
SYNTAX :- DROP DATABASE <database_name>;
EXAMPLE :- DROP DATABASE SCHOOL;

Fun & Easy to follow

Works on all devices

Your own Pace

Super Affordable

STRUCTURE QUERY LANGUAGE

Introduction to SQL

Structured Query Language (SQL) is the most popular query language used by major relational database management systems such as MySQL, ORACLE, SQL Server, etc. SQL is easy to learn as the statements comprise of descriptive english words and are not case sensitive.

SQL provides statements for defining the structure of the data, manipulating data in the database, declaring constraints and retrieving data from the database in various ways, depending on your requirements.

SQL provides variety of tasks such as

Advantage & Disadvantage of MY SQL

Advantages of MySQL:

Disadvantages of MySQL:

SQL STATEMENTS

SQL command or statement is a special kind of sentence that contains clauses and all end with Semicolon;

DDL(Data Definition Language) :

DML(Data Manipulation Language):

DCL(Data Control Language) :

TCL (Transaction Control Language)

Rules for SQL commands

Rules for SQL commands are given below.

SQL DATA TYPES

SQL Command Basic

SQL database is a way of organizing a group of table and table stores the data in the form of rows and columns

CREATING AND USING A DATABASE :

SYNTAX :- CREATE DATABASE DATABASE_NAME

EXAMPLE :- CREATE DATABASE SCHOOL;

HOW TO SELECT DATABASE :

SYNTAX :- USE DATABASE_NAME;

EXAMPLE :- USE STUDENT;

SHOW DATABASES :

SYNTAX :- SHOW DATABASES;

EXAMPLE :- SHOW DATABASES;

DROPPING a DATABASE

SYNTAX :- DROP DATABASE <database_name>;

EXAMPLE :- DROP DATABASE SCHOOL;

DDL STATEMENTS / COMMANDS

CREATING STATEMENT :

SYNTAX :- CREATE TABLE <TABLE_NAME>(<column_name ><data_type>[(<size>)][constraints],

<column_name ><data_type>[(<size>)][constraints],

<column_name ><data_type>[(<size>)][constraints]

);

EXAMPLE :-

Create table Student(No int primary key,Name varchar(50),age int,department varchar(50),DOA date,fee int,gender varchar(6));

CONSTRAINTS

DROPPING a DATABASE

SYNTAX :- DROP DATABASE <database_name>;

EXAMPLE :- DROP DATABASE SCHOOL;

DROPPING a DATABASE

SYNTAX :- DROP DATABASE <database_name>;

EXAMPLE :- DROP DATABASE SCHOOL;

Popular Videos

UX for Teams

SEO & Instagram

Leave a Reply Cancel reply

sign up!

Create table Student
(
No int primary key,
Name varchar(50),
age int,
department varchar(50),
DOA date,
fee int,
gender varchar(6)
);