PRACTICAL NO.
1
SQL COMMAND
Data Definition Language Command (DDL)
This section of the article will give you an insight into the
commands through which you can define your database. The commands are
as follow.
1. Create
2. Drop
3. Truncate
4. Alter
Data Manipulation Language (DML)
Statements allow you to query, edit, add, and remove data stored
in database objects. The primary DML commands are SELECT, INSERT,
DELETE, and UPDATE. Using DML statements, you can perform powerful
actions on the actual data stored in your system.
5. Insert
6. Update
7. Delete
Create Database:
This statement is used to create a table or a database.
The “Create Database” statement as the name suggest, this
statement is used to create a database.
Syntax:
Create Database Database Name;
Example: CREATE DATABASE STEDENTS;
PRACTICAL NO.2
Use Table:
This statement is used to giving permission for used created database.
Syntax:
USE DATABASE NAME;
Example:
USE STUDENT;
PRACTICAL NO.3
Create Table:
This statement is used to create a tables in created database.
Syntax:
CREATE TABLE Table Name(Colunmn no.1 datatype, Colunmn
no.2,datatype, Colunmn no.3 datatype);
Example:
create table teacher_tbl
(
teacheID int primary key,
FirstName varchar(20),
LastName varchar(15),
Salary float,
MobileNo varchar(10)
);
PRACTICAL NO.4
Insert
This statement is used to Insert multiples table in created database.
Syntax:
INSERT INTO TABLE NAME VALUES(value1,value2,value3,value4……..);
Example:
insert into teacher_tbl values(1,'Gauri','Bhagat',20000,7218212366);
PRACTICAL NO.5
Select
This statement is used to show tables in created database.
Syntax:
SELECT *FROM TABLE NAME;
Example:
Select *from teacher_tbl;
PRACTICAL NO.6
Delete
This statement is used to Delete tables in created database.
Syntax:
DELETE FROM TABLE NAME WHERE CONDITION;
Example:
delete from teacher_tbl where teacheID=1;
PRACTICAL NO.7
After delete table:
This command is used to check tables after delete command
execute in database.
Syntax:
SELECT *FROM TABLE NAME;
Example:
select *from teacher_tbl;
PRACTICAL NO.8
Update table
This command is used to Update any value in database.
Syntax:
UPDATE table_name. SET column1 WHERE value1;
Example:
update teacher_tbl set FirstName='Sonali'where teacheID=2
PRACTICAL NO.9
After update table:
This command is used to check tables after upadate command execute in
database.
Syntax:
SELECT *FROM TABLE NAME;
Example:
select *from teacher_tbl
PRACTICAL NO.10
NOT NULL
In SQL, the NOT NULL constraint in a column means that the column cannot
store NULL values.
Syntax:
CREATE TABLE table_name ( column_name data_type NOT NULL )
Example:
create database TEACHER1
use TEACHER1
create table TEACHER1_tbl
(
TEACHER1ID int primary key,
FirstName varchar(20) NOT NULL,
LastName varchar(15),
Salary float,
MobileNo varchar(10)
);
PRACTICAL NO.11
DROP TABLE COMMAND TABLE
This command is used to drop an existing table or a database.
Syntax:
DROP DATABASE TABLE NAME;
Example:
DROP DATABASE TEACHER;
PRACTICAL NO.12
SELECT “M” TABLE IN DATABASE TABLES
This command is used to show only selected tables in database table.
Syntax:
SELECT *FROM TABLE NAME WHERE CONDITION;
Example:
select *from teacher_tbl where Gender ='M';
PRACTICAL NO.13
SELECT ORDER BY TABLE
The ORDER BY keyword sorts the records in ascending order by
default. To sort the records in descending order, use the DESC keyword
Syntax:
SELECT column-list FROM table_name [ORDER BY column1, column2,
.. columnN] [ASC | DESC];
Example:
select FirstName,LastName from teacher_tbl order by FirstName,LastName;
PRACTICAL NO.14
COUNT SUM OF TOTAL SALARY
In this SQL SUM Function example, we've aliased the SUM(salary)
expression as "Total Salary". As a result, "Total Salary" will display as the
field name when the result set is returned.
Syntax:
SELECT SUM(SALARY) AS TOTAL SALARY FROM TABLE NAME;
Example:
select SUM(Salary) as Total_salary from teacher_tbl;
PRACTICAL NO.15
COUNT MAXIMUN AND MINIMUM SALARY OF TABLE
From the following table, write a SQL query to find those employees
whose salary is equal or more to the average of maximum and minimum
salary.
Syntax:
SELECT MAX(salary), MIN(salary) AS MIN FROM TABLE NAME ;
Example:
select MAX(Salary) as MAX_Salary, MIN(Salary) as MIN_Salary from
teacher_tbl;