realcode4you
- Mar 5, 2023
- 11 min read

SQL Database Assignment Help In India | Complete SQL Tutorial

Creating Database with MySQL

> mysql -uroot -p
after this type your password.
Then create database like employee
mysql> create database employee;
Then use database
mysql> use employee;
Then create table
mysql> create table Naveen;

SQL Tutorial:

SQL - Introduction:

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database.

SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

However, the standard SQL commands such as "Select", "Insert", "Update","Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database

SQL - Table Fundamentals

A table is database object that holds user data

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables.

Each table in SQL associated with the specific data types-

Char(size) : This data type is used to store character strings values of fixed length.

Varchar(size) : This data type is used to store variable length alphanumeric data.

Date(size) : This data is used to represent date and time.

Number(size) : The number data type is used to store numbers(fixed or floating point).

Long(size) : This data type is used to store variable length character strings containing upto 2GB.

Raw/Long raw: The RAW/LONG RAW data tupes is used to store binary data, such as digitized picture or image.

SQL - Create Table Command

Table creation is done using the create table syntax.

Syntax:

CREATE TABLE tablename (
    columnname1 datatype(size) constraints(if required),
    columnname2 datatype(size) ,
    columnname3 datatype(size) ,
    .
    .
    .
    columnnameN datatype(size) 
);

Create table command defines each column of the table uniquely. Each column has a minimum three attribute, a name, datatype and size(i.e. column width)

Example:

Create table employee
CREATE TABLE employee (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int,
    Address varchar(100),
    City varchar(50),
    State varchar(50)
);

Syntax:

describe <tablename>;

SQL- Inserting data into table

The insert statement is used to insert or add a row of data into the table.

Once Table is created the most natural thing to do is load this table with data to be manipulated later.

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis.

Syntax:

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

Example:

INSERT INTO employee (ID, lastName, FirstName, Age, Address, City, State)
VALUES (101570, ‘Kumar’, ‘Naveen’, 28, ‘XYZ’, ‘Etah’, ‘UP’);

#please check single inverted comma when you insert value in database using cmd.

Note: Character statement placed within the insert into statement must be enclosed in single quotes(‘).

It have a one to one relationship, means we insert one value in each column at a time.

SQL - Viewing data in the Table

The select statement is used to query the database and retrieve selected data that match the criteria that you specify.

Syntax:

SELECT column1, column2, ...
FROM table_name;
Or 
SELECT * FROM table_name;

Example:


SELECT FirstName, LastName FROM employee;
or
SELECT *FROM employee;

Note: In this we have use meta character asterisk(*), which used to expand all columns of the table.

SQL- Filtering table data

SQL must give us a method of filtering out data that is not required.

Three way of filtering table data will be:

1-Selected columns and all rows

2-Selected rows and all columns

3-Selected columns and selected rows

Selected columns and all rows

-------------------------------

Retrieval of specific column from a table.

Syntax:

SELECT column1, column2, ... FROM table_name;

Example:

SELECT FirstName, LastName FROM employee;

Selected rows and all columns:

-----------------------------

Syntax:

SELECT FROM table_name WHERE <condition>;

Example:

SELECT *FROM employee WHERE FirstName = “naveen”;

Selected columns and selected rows

----------------------------------

To view the specific set of rows and columns.

Syntax:

SELECT column1, column2, ... FROM table_name WHERE <condition>;

Example:

SELECT lastName, FirstName, ... FROM employee WHERE City = “etah”;

SQL- Eliminating duplicate row using select statement

If table could hold duplicate rows, in this case it necessary to remove delicacy using this

Syntax:

SELECT DISTINCT lastName, FirstName, ... FROM employee;

SELECT DISTINCT *FROM employee;

Example:

Select DISTINCT city from employee;

-------------------------------------------------------

SQL - Sorting data in a table

The rows retrieved from table in both ascending or descending order.

Syntax:

Select *from tablename ORDER BY columnname1, columnname2 <[sort order]>;

Where [sort order] means either “desc”, by default it take ascending order.

Example:

Select *from employee ORDER BY firstname
or
Select *from employee ORDER BY firstname desc;

SQL – Creating a table from a table

In this we create one table by using another table

In this “AS SELECT” syntax use to select data from one table.

Syntax:

Create table <tablename><[(columnname,columnname)]> AS SELECT <columnname>, <columnname> from <tablename>;

Example:

Create table employee1(firstname1,lastname1) as select fristname, lastname from employee;

SQL-Inserting data into a table from another table

After creating table from source table if you want to insert record into the new table use this procedure

Syntax:

Insert into <tablename> select <columnname1>, <columnname2> from <tablename>;

Example:

Insert into employee1 select firstname, lastname from employee;

Insert selected record from another table

Example:

Insert into employee1 select firstname, lastname from employee where firstname = “naveen”;

SQL – Delete Operation

The verb DELETE used to remove:

All rows from a table

A rows from a table

Syntax:

Delete from <tablename>;
Or
Delete from <tablename> where <condition>;

Example:

Delete all rows-

Delete from employee;

Delete selected row-

Delete from employee where firstname = “naveen”;

SQL - Updating content of a table

-------------------------------------------------------

The update command used to change or modify data values in a table.

To update –

All the rows

Selected row

All the rows

Syntax:

Update <tablename> set <columnname> = <expression>, <columnname> = <expression>;

Example:

Update employee set address = “noida”;

Selected row

Example:

Update employee set address = “noida” where firstname = “naveen”;

SQL – Dropping a column from a table

Use alter command to drop column form a table

Syntax:

Alter table <tablename> drop column <columnname>;

Example:

Alter table employee drop column address;

SQL – Modifying Existing columns

Syntax:

Alter table <tablename> modify (<columnname> <newdatatype>(<newsize>));

Example:

Alter table employee modify (address varchar(50));

Here we modigy size of columnname address.

Restriction of alter table : these cannot perform

Change the name of the table

Change the name of column

Decrease the size of column if table data exists

SQL – Renaming, truncate, and destroying tables

Rename:

Use for rename the exiting table

Syntax:

Rename <oldtablename> to <newtablename>;

Example:

Rename employee to employee2;

Truncating table:

Syntax:

Truncate table <tablename>;

Example:

Truncate table employee;

Destroying table:

Syntax:

Drop table <tablename>;

Example:

Drop table employee;

SQL - Key constraints

SQL constraints are used to specify rules for data in a table.

SQL Create Constraints
CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified

INDEX- Used to create and retrieve data from the database very quickly

SQL – UNIQUE and NOT NULL constraints

NOT NULL constraints

The NOT NULL constraint enforces a column to NOT accept NULL values.

Example:

CREATE TABLE employee (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

UNIQUE constraints

The UNIQUE constraint ensures that all values in a column are different.

Example:

CREATE TABLE employee (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

SQL – Primary key

What is primary key?

A column or columns is called primary key that uniquely identifies each row in the table.

If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.

What is Composite key?

When multiple columns are used as a primary key, it is known as composite primary key.

Example:

CREATE TABLE employee (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

CREATE TABLE employee (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Using with multiple columns

PRIMARY KEY (columnname1,columnname2..)

SQL – Foreign key

A Foreign key is constraint that enforces referential integrity in SQL server database.

It uses a column or combination of columns that is used establish link between the data in two tables to control the data that can be stored in the foreign key table.

Let suppose two tables are:

Customers and Orders

Customers table has a field like- Cus_ID(Primary key), Cus_Name, Cus_address

Orders table has a field like- ID(primary key), Order_id(foreign key), Order_address

Then syntax can be define as:

MySQL-

CREATE TABLE Orders
ID integer,
 (Order_Id integer, 
 Order_address varchar(50), 
 Primary Key (ID), 
 Foreign Key (Order_id) REFERENCES Customers(Cus_ID));

SQL server and Oracle -

CREATE TABLE Orders
ID integer Primary Key,
 (Order_Id integer Foreign Key, 
 Order_address varchar(50), 
Order_id integer REFERENCES Customers(Cus_ID));

SQL – Check, Default and Index Constraints

Check Contraints

It use as a condition and check the value of columns.

CREATE TABLE employee (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT CHK_Employee CHECK (Age>=18)
);

Defaults Constraints

It used to provide default value for a column.

Example:

Running on My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE employee (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    Address varchar(100) DEFAULT ‘Etah’
);

Index Constraints

Used to create indexes in tables

It used for speed up data.

Syntax:

CREATE INDEX index_name ON table_name;

SQL – Aggregate function

In this section we will learn about aggregate function.

There are different types of aggregate function which is used in SQL

AVG, COUNT, MAX, MIN, SUM, LTRIM, RTRIM, POWER, ROUND, etc.

AVG:

Returns the average value of ‘n’

Syntax:

Avg([<DISTINCT>|<ALL>] <expr>)

Example:

Table structure with data

CREATE TABLE `employee` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `firstname` varchar(50) DEFAULT NULL,
 `lastname` varchar(50) DEFAULT NULL,
 `salary` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*Data for the table `employee` */

insert into `employee`(`id`,` firstname `, ‘lastname’, `salary`) values (1,'Naveen', ‘kumar’,15000),(2,’jitendra’,’singh’,16000),(3,'sachin',‘pandit’,15000),(4,'adarsh',’kumar’,18000);

SELECT AVG(salary) FROM employee;
SELECT count(salary) FROM employee;
SELECT max(salary) FROM employee;
SELECT min(salary) FROM employee;
SELECT sum(salary) FROM employee;

SQL like

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters

_ - The underscore represents a single character

LIKE 'a% - Finds any values that start with "a"
LIKE '%a' - Finds any values that start with "a"
LIKE '%or%' - Finds any values that have "or" in any position
Finds any values that have "r" in the second position
Finds any values that start with "a" and are at least 3 characters in length
Finds any values that start with "a" and ends with "o"

Example:

WHERE SALARY LIKE '50%'
WHERE SALARY LIKE '%100%'
WHERE SALARY LIKE '_00%'
WHERE SALARY LIKE '5_%_%' : Finds any values that start with 2 and are at least 3 
characters in length.
WHERE SALARY LIKE '%3'
WHERE SALARY LIKE '_1%2' - Finds any values that have a 2 in the second position and
 end with a 3.
WHERE SALARY LIKE '1___3' : Finds any values in a five-digit number that start with 2 
and end with 3.

SQL Logic Operator

Logic Operator that can be used in SQL –

AND Operator
OR Operator
NOT Operator

AND

Syntax:

Select columnname1, columnname2…column3 from tablename
Where columnname1..columnnameN > 50 AND columnname1..columnnameN < 100 ;

Example:

Select salary from employee where salary >25000 AND salary<30000;

As you can use any remaining OR and NOT operator.

SQL Range Serching – Between, IN and NOT IN

Between

In order to select data within the range of values, the BETWEEN operator is used. It used to select rows that contains values within a specified lower and upper limit.

Syntax:

Select columname1, columnname2…columnnameN from tablename
Where cloumnname BETWEEN 10 AND 50;

Example:

Select id, name, salary from employee
Where salary BETWEEN 25000 AND 30000;

IN Predicate

The arithmetic operator (=) compare a single value to another single value. In case a value needs to be compared to a list of values then the IN predicate is used.

Syntax:

Select columname1, columnname2…columnnameN from tablename
Where cloumnname IN (value1,value2, ….)

Example:

Select id, name, salary from employee
Where name IN (‘naveen’,’jitendra’,’adarsh’);

You can try NOT IN itself.

SQL – Group BY and Having clauses

Used to selecting and retrieval of rows

Group By clauses:

Group By clause creates a data set, containing several sets of records grouped together based on a condition.

Example:

Select product_no,sum(Ordered_quantity) “Total ordered quantity” from sales_order group by product_no

Having Clause:

“Having” imposes a condition on the “Group By” clause, which further filters the groups created by the Group By clause

Select product_no,sum(Ordered_quantity) “Total ordered quantity” from sales_order  GROUP BY product_no HAVING product_no = ‘p001’ OR product_no = ‘p004’

output:

SQL – Subqueries

A subqueries is a form of an SQL statement that appears inside another SQL statement. It used to handle nested queries.

It can be used to handle some commands:

To insert record in a target table
To create tables and insert records in the table created
To update record in a target table
To create views
It used to handle SELECT, UPDATE, AND DELETE Statements with WHERE, HAVING, IN and so on.

Example:

Let suppose two table first is students and courses

Students:

Stu_courses:

Select *from students where Stu_id = (select stu_id from stu_courses where courses =’ mca’);

output:

SQL – Join

In SQL, Joins help retrieving data from two or more database tables.

The tables are mutually related using primary and foreign keys.

Types of join:

Inner join
Left outer join
Right outer join
Full join
Self join

Let’s start with these two tables:

Students:

Stu_courses:

Inner join

The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.

SELECT Stu_courses.courses, Students.stu_name, Students.address FROM Students

INNER JOIN Stu_courses

ON Students. Stu_id = Stu_courses. Stu_id;

Left outer join

This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN

SELECT Students.stu_name, Stu_courses. Courses

FROM Students

LEFT JOIN Stu_courses

ON Stu_courses. Stu_id = Students. Stu_id

Right outer join

RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN

SELECT Students. stu_name, Stu_courses. Courses

FROM Students

RIGHT JOIN Stu_courses

ON Stu_courses. Stu_id = Students. Stu_id;

Full Join

FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.

SELECT Students.stu_name, Stu_courses.Courses

FROM Students

FULL JOIN Stu_courses

ON Stu_courses. Stu_id = Students. Stu_id;

SQL- UNION, INTERSECTION AND MINUS clause

Union Clause

Record only in query two Record only in Query one It merges the output of two or more queries into a single set of rows and columns.

It picks up the common records as well as individual records in both quries.

Example:

Let’s start with these two tables:

Students:

Stu_courses:

Select stu_name “Name”, address from students where stu_id = “101570”
UNION select Courses “Course”, Year from Stu_courses where  stu_id = “101570”;

Output:

SQL VIEW

In SQL, a view is a virtual table OR we can say it is the duplicate copy of real table, based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database

Reason why views are created are

When data security is required
When Data redundancy is to be kept to the minimum while maintaining data security.

How a view is

Created
Destroyed

Creating view:

create view <viewname> 
as select
 <columnname1>, <columnname2> 
from <tablename> 
where <columnnamed> = <expression list>;
group by <grouping criteria>
having <predicate>

Operation (INSERT, UPDATE AND DELETE) perform on view

Here we can explain it with the help of example in which we use a students table.

Example:

Students:

First we will create view of the students table

CREATE VIEW:

create view vw_students AS 
SELECT Stu_id, Stu_name, Address 
FROM Students;

INSERT OPERATION:

Insert into vw_students values (‘101576’,’nnn’,’752 E west’);

UPDATE OPERATION:

update vw_students set Stu_id = ‘101578’ where stu_name = ‘Arvind kumar’;

DELETE OPERATION:

delete from vw_students where Stu_id = ‘101570’;

SQL – Security Management(Granting and Revoking Permissions)

Here, used right which allow use of some or all resources on the server are called Privileges.

Granting of Privileges - Owner of the object will have to give permissions

Revoking of Privileges - Privileges once give can be taken back by the owner of the object. This is called Revoking of Privileges.

Granting of Privileges

Syntax:

GRANT <object privileges>
ON <objectname>
TO <username>
[WITH GRANT OPTION];
The list of object privileges is as follows:
ALTER, DELETE, INDEX, INSERT, SELECT and UPDATE

Example1:

Give all data manipulation permissions to the user Naveen

GRANT ALL
ON Students
TO naveen;

Example2:

Give SELECT and UPDATE data manipulation permissions to the user Naveen 
GRANT SELECT, UPDATE
ON Students
TO naveen;

Revoking of Privileges

The Revoke statement is used to deny the grant given on an object.

Syntax:

REVOKE <object privileges>
ON <objectname>
FROM <username>;

RealCode4You

SQL Database Assignment Help In India | Complete SQL Tutorial

Example:

In this we create one table by using another table

In this “AS SELECT” syntax use to select data from one table.

Syntax:

SQL like

Example:

Recent Posts