PROJECT OF
SQL
Submitted by: Suman Paudel
Roll No: 24
Submitted To:
Semester: second (2nd)
kedarnath paudel
Exam Roll No: 23817/19
STRUCTURED QUERY
LANGUAGE (SQL)
SQL is a domain-specific language used in programming and designed for
managing data held in a relational database management system, or for stream
processing in a relational data stream management system.
What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL became a standard of the American National Standards Institute
(ANSI) in 1986, and of the International Organization for
Standardization (ISO) in 1987
What Can SQL do?
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
Types of SQL
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
[Link] definition language
A data definition or data description language is a syntax similar to a computer
programming language for defining data structures, especially database schemas.
DDL statements create and modify database objects such as tables, indexes, and
users. Common DDL statements are CREATE, ALTER, and DROP
a. CREATE statement
- It is used to create object in the databases.
Syntax: For create database;
CREATE DATABASE database name;
For create table;
CREATE TABLE table name
(column1 datatype(size),column2 datatype(size),column3 datatype(size),……);
b. ALTER statement
- It is used to alter the structure of databases.
Syntax:
ALTER TABLE table_name
ADD column name datatype(size);
c. DROP statement
- It is used to delete column or objects from the database.
Syntax:
ALTER TABLE table_name
DROP column name;
[Link] manipulation language
A data manipulation language is a computer programming language used for
adding, deleting, and modifying data in a database. A DML is often a sublanguage
of a broader database language such as SQL, with the DML comprising some of the
operators in the language.
a. SELECT statement
- It is used to select data from tables.
Syntax:
SELECT*from table name;
b. INSERT statement
- It is used to insert data into a table.
Syntax:
INSERT into table name VALUES(value1,value2,value3,
……),VALUES(value1,value2,value3,…);
c. DELETE statement
- It is used to delete records from a table.
Syntax:
DELETE from table name where <condition>;
d. UPDATE statement
- It is used to update existing data within a table;
Syntax:
UPDATE table name set column name=’value’ where <condition>;
MYSQL
RESULT
OR
OUTPUT
mysql: [Warning] C:\Program Files\MySQL\MySQL Server
8.0\bin\[Link]: ignoring option '--no-beep' due to
invalid value ''.
Enter password: *******(Dmcbba)
Welcome to the MySQL monitor. Commands end with ; or \
g.
Your MySQL connection id is 15
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All
rights reserved.
Oracle is a registered trademark of Oracle Corporation
and/or its
affiliates. Other names may be trademarks of their
respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| bimala |
| pramila |
+--------------------+
6 rows in set (0.00 sec)
mysql> create database suman;
Query OK, 1 row affected (0.15 sec)
Run school database “GO”
mysql> use suman;
DROP database suman;
(use “go” button )
Database changed
mysql> create table students(sid int(20),sname
varchar(30),srank varchar(20));
Query OK, 0 rows affected, 1 warning (0.62 sec)
Other Example
mysql> CREATE TABLE Students (
id int,
name VARCHAR (100),
district VARCHAR (100),
gender enum (‘M’,’F’,’o’),
dob date,
total_fee float,
paid_fee float
);
if you delete table use following sql command
mysql>DROP TABLE students;
mysql> describe students;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(20) | YES | | NULL | |
| sname | varchar(30) | YES | | NULL | |
| srank | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> insert into students values(1,'Ram','frist'),
(2,'Hari','second');
Query OK, 2 rows affected (0.20 sec)
Records: 2 Duplicates: 0 Warnings: 0
Other method to insert data into students table
mysql> INSERT INTO Students ( id, name, district ,
gender ,dob , total_fee , paid_fee )
VALUES (1,’bishwa paudel’ ,’kaski’,’M’ , ‘1997-05-
14’ ,5000 , 500 );
Show table data
mysql> select *from students;
+------+---------+--------+
| sid | sname | srank |
+------+---------+--------+
| 1 | Ram | frist |
| 2 | Hari | second |
+------+---------+--------+
2 rows in set (0.00 sec)
Delete table
mysql> DROP table students;
mysql> alter table students add saddress varchar(30);
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
Show table data
mysql> select *from students;
+------+---------+--------+----------+
| sid | sname | srank | saddress |
+------+---------+--------+----------+
| 1 | Ram | frist | NULL |
| 2 | Hari | second | NULL |
+------+---------+--------+----------+
2 rows in set (0.00 sec)
Delete table line
mysql> Delete from student where id =3;
(3 number line delete)
mysql> update students set saddress ='pokhara' where
sname ='Ram';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update students set saddress ='baglung' where
sname ='Hari';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from students;
+------+---------+--------+----------+
| sid | sname | srank | saddress |
+------+---------+--------+----------+
| 1 | Ram | frist | pokhara |
| 2 | Hari | second | baglung |
+------+---------+--------+----------+
2 rows in set (0.00 sec)
mysql> select distinct sname from students;
+---------+
| sname |
+---------+
| Ram |
| Hari |
+---------+
2 rows in set (0.03 sec)
mysql> select sid,sname from students;
+------+---------+
| sid | sname |
+------+---------+
| 1 | Ram |
| 2 | Hari |
+------+---------+
2 rows in set (0.00 sec)
mysql> select *from students;
+------+---------+--------+----------+
| sid | sname | srank | saddress |
+------+---------+--------+----------+
| 1 | Ram | frist | pokhara |
| 2 | Hari | second | baglung |
+------+---------+--------+----------+
2 rows in set (0.00 sec)
mysql> delete from students where sname ='Hari';
Query OK, 1 row affected (0.13 sec)
mysql> select *from students;
+------+---------+-------+----------+
| sid | sname | srank | saddress |
+------+---------+-------+----------+
| 1 | Ram | frist | pokhara |
+------+---------+-------+----------+
1 row in set (0.00 sec)
mysql> drop table students;
Query OK, 0 rows affected (0.40 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| bimala |
| pramila |
| Suman |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database suman;
Query OK, 0 rows affected (0.20 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| bimala |
| pramila |
+--------------------+
6 rows in set (0.07 sec)
mysql>
select *from students where age =25;
(show all 25 age students)
select fname,lname from students where age =25;
select * from students where fname=’naran’;
select age from students order by age;
select fname from sudents order by fname limit 3;
select fname from sudents where fname like ‘%in%;
select fname from sudents where fname like ’k%’;
select *from students where age >25;
select * from students where age between 22 and 25;
select * from students where fname in (‘vinod’,’binita’);
mysql>exit;
-THE
END-