ASD Lab Manual JECC Queries
ASD Lab Manual JECC Queries
ASD Lab Manual JECC Queries
Ex. No: 1
Creation of a database and writing mysql queries to retrieve information from the database.
AIM
Creation of a database using DDL commands
COMMANDS
Database changed
mysql> create table student (stname varchar(30), stid varchar(10), stage int(2), starea varchar(20));
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stname | varchar(30) | YES | | NULL | |
| stid | varchar(10) | YES | | NULL | |
| stage | int(2) | YES | | NULL | |
| starea | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
1
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stname | varchar(30) | YES | | NULL | |
| stid | varchar(10) | YES | | NULL | |
| stage | int(5) | YES | | NULL | |
| starea | varchar(20) | YES | | NULL | |
| stdept | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stname | varchar(30) | YES | | NULL | |
| stid | varchar(10) | YES | | NULL | |
| stage | int(5) | YES | | NULL | |
| starea | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stname | varchar(30) | YES | | NULL | |
| stid | varchar(10) | YES | | NULL | |
| stage | int(5) | YES | | NULL | |
| starea | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
3
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No : 2
Performing DML commands like Insertion, Deletion, Modifying, Altering, and Updating
records based on conditions.
AIM
To manipulate database using DML commands.
COMMANDS
CREATION OF TABLE
mysql> create table student (stname varchar(30), stid varchar(10), stage int(2), starea varchar(20),
stbranch varchar(20));
+---------+------+-------+-------------+--------------+
| stname | stid | stage | starea | stbranch |
+---------+------+-------+-------------+--------------+
| ashly | 101 | 19 | nehru nagar | aeronautical |
| havesh | 102 | 18 | ayyanthole | marine |
| ruthvik | 103 | 20 | nehru nagar | aerospace |
| harith | 104 | 20 | west fort | mechanical |
+---------+------+-------+-------------+--------------+
4 rows in set (0.00 sec)
4
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
ARITHMETIC OPERATION
+---------+--------+
| stname | stidno |
+---------+--------+
| ashly | 201 |
| havesh | 202 |
| ruthvik | 203 |
| harith | 204 |
+---------+--------+
4 rows in set (0.01 sec)
+-------------+
| starea |
+-------------+
| nehru nagar |
| ayyanthole |
| west fort |
+-------------+
3 rows in set (0.00 sec)
+--------+-------+
| stname | stage |
+--------+-------+
| ashly | 19 |
| havesh | 18 |
+--------+-------+
2 rows in set (0.00 sec)
5
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
BETWEEN OPERATOR
mysql> select stname,starea, stid from students where stid between 102 and 104;
+---------+-------------+------+
| stname | starea | stid |
+---------+-------------+------+
| havesh | ayyanthole | 102 |
| ruthvik | nehru nagar | 103 |
| harith | west fort | 104 |
+---------+-------------+------+
3 rows in set (0.01 sec)
PATTERN MATCHING
mysql> select stname, starea from students where starea like '%g%';
+---------+-------------+
| stname | starea |
+---------+-------------+
| ashly | nehru nagar |
| ruthvik | nehru nagar |
+---------+-------------+
2 rows in set (0.00 sec)
mysql> select stname ,stid from students where stid>102 and starea='nehru nagar';
+---------+------+
| stname | stid |
+---------+------+
| ruthvik | 103 |
+---------+------+
1 row in set (0.00 sec)
mysql> select stname ,stid from students where stid>102 or starea='nehru nagar';
+---------+------+
| stname | stid |
+---------+------+
| ashly | 101 |
| ruthvik | 103 |
| harith | 104 |
+---------+------+
3 rows in set (0.00 sec)
6
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
+---------+------+-------+-------------+--------------+----------+
| stname | stid | stage | starea | stbranch | stpocket |
+---------+------+-------+-------------+--------------+----------+
| ashly | 101 | 19 | nehru nagar | aeronautical | 750 |
| havesh | 102 | 18 | ayyanthole | marine | 500 |
| ruthvik | 103 | 20 | nehru nagar | aerospace | 250 |
| harith | 104 | 20 | west fort | mechanical | 100 |
+---------+------+-------+-------------+--------------+----------+
4 rows in set (0.00 sec)
7
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
DELETION
+---------+------+-------+-------------+------------+----------+
| stname | stid | stage | starea | stbranch | stpocket |
+---------+------+-------+-------------+------------+----------+
| havesh | 102 | 18 | ayyanthole | marine | 500 |
| ruthvik | 103 | 20 | nehru nagar | aerospace | 250 |
| harith | 104 | 20 | west fort | mechanical | 100 |
+---------+------+-------+-------------+------------+----------+
3 rows in set (0.00 sec)
8
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No : 3
AIM
To manipulate database using DCL commands.
COMMANDS
You can grant users various privileges to tables. These permissions can be any combination of
SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT OPTION or
ALL.
Syntax
Once you have granted privileges, you may need to revoke some or all of these privileges. To do
this, you can run a revoke command. You can revoke any combination of SELECT, INSERT,
UPDATE, DELETE, REFERENCES, ALTER, or ALL.
Syntax
mysql -u root -p
use class;
create table stud (id int primary key auto_increment,name char(20),age int);
mysql -u UI@localhost -p
use class;
create table students (id int primary key auto_increment,name char(50),dob date, age int);
mysql -u root -p
use class;
mysql -u UI@localhost -p
use class;
create table student (id int primary key auto_increment,name char(50),dob date, age int);
show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| stud |
| student |
+-----------------+
mysql -u GUI@localhost -p
use class;
create table student (id int primary key auto_increment,name char(50),dob date, age int);
mysql -u root -p
mysql -u UI@localhost -p
use class;
mysql -u GUI@localhost -p
11
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No : 4
AIM
To manipulate database using TCL commands.
SET autocommit disables or enables the default autocommit mode for the current session.
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a
statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent.
The change cannot be rolled back.
create table stud (id int primary key auto_increment,name char(50),dob date,age int);
start transaction;
+----+-------------+-------------------+------+
| id | name | dob | age|
+----+-------------+-------------------+------+
| 1 | naveen | 1997-08-20 | 20 |
| 2 | sathya | 1996-09-10 | 21 |
+----+------------+--------------------+------+
ROLLBACK;
+----+-------------+-------------------+------+
| id | name | dob | age|
+----+-------------+-------------------+------+
| 1 | naveen | 1997-08-20 | 20 |
| 2 | sathya | 1996-09-10 | 21 |
+----+------------+--------------------+------+
12
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
SET autocommit=0 ;
savepoint s1;
+----+-------------+-------------------+------+
| id | name | dob | age|
+----+-------------+-------------------+------+
| 1 | naveen | 1997-08-20 | 20 |
| 2 | sathya | 1997-09-10 | 21 |
+----+------------+--------------------+------+
ROLLBACK to s1;
+----+-------------+-------------------+------+
| id | name | dob | age|
+----+-------------+-------------------+------+
| 1 | naveen | 1997-08-20 | 20 |
| 2 | sathya | 1996-09-10 | 21 |
+----+------------+--------------------+------+
+----+-------------+-------------------+------+
| id | name | dob | age|
+----+-------------+-------------------+------+
| 1 | naveen | 1997-08-20 | 20 |
| 2 | sathya | 1997-09-10 | 20 |
+----+------------+--------------------+------+
commit;
+----+-----------+--------------------+--------+
| id | name | dob | age |
+----+------------+-------------------+--------+
| 1 | naveen | 1997-08-20 | 20 |
| 2 | sathya | 1997-09-10 | 20 |
| 3 | kathir | 1995-06-15 | 22 |
+----+-------------+-------------------+-------+
13
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
ROLLBACK;
+----+-------------+-------------------+------+
| id | name | dob | age|
+----+-------------+-------------------+------+
| 1 | naveen | 1997-08-20 | 20 |
| 2 | sathya | 1997-09-10 | 20 |
+----+------------+--------------------+------+
14
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No : 5
AIM
UNION is used to combine the result from multiple SELECT statements into a single result set.
mysql> select id from sub where id IN (select id from students where age=13);
+-------+
| id |
+-------+
| 12347 |
+-------+
1 row in set (0.00 sec)
mysql> select id,name from students where EXISTS (select sname from sub where id=students.id);
+-------+--------+
| id | name |
+-------+--------+
| 12346 | arjith |
| 12347 | arjith |
+-------+--------+
2 rows in set (0.00 sec)
mysql> select id,name from students where NOT EXISTS (select sname from sub where
id=students.id);
+-------+------+
| id | name |
+-------+------+
| 12345 | |
| 12348 | |
| 12349 | NULL |
+-------+------+
3 rows in set (0.00 sec)
16
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No : 6
AIM
Stored Functions
A stored function is a special kind stored program that returns a single value. You use stored
functions to encapsulate common formulas or business rules that are reusable among SQL
statements or stored programs. Different from a stored procedure, you can use a stored function in
SQL statements wherever an expression is used. This helps improve the readability and
maintainability of the procedural code.
Syntax
BEGIN
declaration_section
executable_section
END;
mysql> DELIMITER $$
mysql>
mysql> CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE lvl varchar(10);
RETURN (lvl);
END
$$
Query OK, 0 rows affected (0.00 sec)
17
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
mysql>
18
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No: 7
AIM
Syntax
BEGIN
declaration_section
executable_section
END;
DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(IN orderStatus VARCHAR(25),OUT total INT)
BEGIN
SELECT count(orderNumber) INTO total FROM orders
WHERE status = orderStatus;
END$$
DELIMITER ;
CALL CountOrderByStatus('Shipped',@total);
SELECT @total;
Delimiter
19
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
The delimiter is the character or string of characters which is used to
complete an SQL statement. By default we use semicolon (;) as a delimit-
er. But this causes problem in stored procedure because a procedure can
have many statements, and everyone must end with a semicolon. So for your
delimiter, pick a string which is rarely occur within statement or within
procedure
mysql> DELIMITER $$ ;
Now the default DELIMITER is "$$". Let execute a simple SQL command :
mysql> DELIMITER ; $$
TO SET DELIMITER
delimiter $$
select * from Salesman $$
+-------------+--------+---------+------------+
| salesman_id | name | city | commission |
+-------------+--------+---------+------------+
| 5000 | James | NewYork | 0.2 |
| 5001 | Sharon | London | 0.25 |
| 5003 | Hammer | London | 0.25 |
| 5002 | Nail | Paris | 0.3 |
+-------------+--------+---------+------------+
CREATE PROCEDURE
20
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
GIVING PARAMETERS
call getinput("London")$$
+-------------+--------+--------+------------+
| salesman_id | name | city | commission |
+-------------+--------+--------+------------+
| 5001 | Sharon | London | 0.25 |
| 5003 | Hammer | London | 0.25 |
+-------------+--------+--------+------------+
TAKING OUTPUT
21
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
+--------+
| name |
+--------+
| mujeeb |
| neeva |
+--------+
create procedure outin(IN c float, OUT total int)
begin
select count(*) into total from Salesman where commission=c;
if(total>1) then
select distinct city from Salesman where commission=c ;
end if;
end
$$
call outin(0.25,@total)$$
+--------+
| city |
+--------+
| London |
| London |
+--------+
IF ELSE IF STATEMENTS SYNTAX
if (exp) then
statement;
elseif (exp) then
statement;
else
statement;
end if;
FOR LOOP
delimiter #
create procedure insrt(IN num float)
begin declare x int;
set x=0; label1:
loop insert into number values(rand());
set x=x+1;
if x>num then leave label1;
end if;
end loop;
end#
22
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No: 8
To implement cursors
AIM
2 OPEN cursor_name;
Then, you use the FETCH statement to retrieve the next row pointed by the cursor and move the cur-
sor to the next row in the result set.
3 FETCH cursor_name INTO variables list;
After that, you can check to see if there is any row available before fetching it.
Finally, you call the CLOSE statement to deactivate the cursor and release the memory associated
with it as follows:
4 CLOSE cursor_name;
When the cursor is no longer used, you should close it.
When working with MySQL cursor, you must also declare a NOT FOUND handler to handle the situa-
tion when the cursor could not find any row. Because each time you call the FETCH statement, the
cursor attempts to read the next row in the result set. When the cursor reaches the end of the result
set, it will not be able to get the data, and a condition is raised. The handler is used to handle this
condition.
To declare a NOT FOUND handler, you use the following syntax:
5 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
Where finished is a variable to indicate that the cursor has reached the end of the result set. Notice
that the handler declaration must appear after variable and cursor declaration inside the stored pro-
cedures.
The following diagram illustrates how MySQL cursor works.
23
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN
OPEN email_cursor;
get_email: LOOP
IF v_finished = 1 THEN
LEAVE get_email;
END IF;
CLOSE email_cursor;
END$$
Query OK, 0 rows affected (0.09 sec)
mysql>
24
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
mysql> DELIMITER ;
mysql> SET @email_list = "";
Query OK, 0 rows affected (0.00 sec)
mysql> CALL build_email_list(@email_list);
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT @email_list;
+----------------------------+
| @email_list |
+----------------------------+
| jim@jecc;tim@jecc;tom@abc; |
+----------------------------+
1 row in set (0.00 sec)
25
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No: 9
To implement triggers
AIM
A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is executed
or fired whenever an event associated with a table occurs e.g., insert, update or delete.
A SQL trigger is a special type of stored procedure. The main difference between a trigger and a
stored procedure is that a trigger is called automatically when a data modification event is made
against a table whereas a stored procedure must be called explicitly.
Syntax
27
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No: 10
To create views
AIM
A database view is a virtual table or logical table which is defined as a SQL SELECT query with
joins. Because a database view is similar to a database table, which consists of rows and columns,
so you can query data against it. Most database management systems, including MySQL, allow you
to update data in the underlying tables through the database view with some prerequisites.
28
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
desc v1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| phone | int(11) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| dept | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
desc detailed;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(20) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| code | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
create table new (id int auto_increment not null primary key, mark1 int
not null, mark2 int not null);
desc new;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| mark1 | int(11) | NO | | NULL | |
| mark2 | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+----------------+
desc new;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| studentid | int(11) | YES | | NULL | |
| mark1 | int(11) | NO | | NULL | |
| mark2 | int(11) | NO | | NULL | |
+-----------+---------+------+-----+---------+----------------+
insert into new (studentid, mark1, mark2) values (1, 40, 50);
insert into new (studentid, mark1, mark2) values (2, 50, 50);
insert into new (studentid, mark1, mark2) values (3, 50, 60);
insert into new (studentid, mark1, mark2) values (4, 56, 60);
29
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
desc rank;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| studentid | int(11) | YES | | NULL | |
| total | bigint(12) | NO | | 0 | |
+-----------+------------+------+-----+---------+-------+
select * from rank;
+----+-----------+-------+
| id | studentid | total |
+----+-----------+-------+
| 4 | 4 | 116 |
| 3 | 3 | 110 |
| 2 | 2 | 100 |
| 1 | 1 | 90 |
+----+-----------+-------+
30
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Ex. No: 11
To import a database
AIM
URL:https://raw.githubusercontent.com/datacharmer/test_db/master/sakila/s
akila-mv-schema.sql
:https://raw.githubusercontent.com/datacharmer/test_db/master/sakila/saki
la-mv-data.sql
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights re-
served.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> exit
Bye
[10:43:26] pe :: pe20 ➜ ~/Downloads ‹master*› » mysql cseb -u
root -p < sakila-mv-schema.sql
Enter password:
^[[A%
[10:43:39] pe :: pe20 ➜ ~/Downloads ‹master*› » mysql cseb -u
root -p < sakila-mv-data.sql
Enter password:
[10:43:56] pe :: pe20 ➜ ~/Downloads ‹master*› » mysql -u root -
pjoss
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights re-
served.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
32
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
| Database |
+--------------------+
| information_schema |
| cerebrals |
| class |
| college |
| cse |
| cseb |
| jyothi |
| jyothi1 |
| lbook |
| mydb |
| mysql |
| performance_schema |
| phpmyadmin |
| s5csea |
| sakila |
| testdb |
| testdb1 |
+--------------------+
17 rows in set (0.00 sec)
mysql> exit
Bye
[10:44:33] pe :: pe20 âžœ ~/Downloads » mysqldump sakila -u root -p >
backup.sql
Enter password:
[10:45:09] pe :: pe20 âžœ ~/Downloads » mysql -u root -pjoss
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights re-
served.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> exit
Bye
[10:48:26] pe :: pe20 ➜ ~/Downloads ‹master*› » mysql cseb -u
root -p < backup.sql
Enter password:
[10:48:52] pe :: pe20 ➜ ~/Downloads ‹master*› » mysql -u root -
pjoss
Welcome to the MySQL monitor. Commands end with ; or \g.
33
Jyothi Engineering. College Dept of Computer Science & Engg.
CS 333 Application Software Development Lab
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights re-
served.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_cseb |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------
34
Jyothi Engineering. College Dept of Computer Science & Engg.