Install MYSQL shell and MYSQL server
MySQL Shell starts with JS prompt => accepts javascript commands
Convert to sql with command: \sql;
\connect root@localhost
Password is set at time of installing MySQLServer
show databases;
use <schema name>;
show tables;
\exit or \quit to exit out of MySql shell
________________________________________________________________
CREATE DATABASE
__________________________________________________________________
Lab Q I student-society DB
create database studentsociety;
Syntax: CREATE DATABASE [IF NOT EXISTS] database_name.
create table STUDENT( RollNo char(6) primary key, StudentName varchar(20), Course
varchar(10), DOB date);
create table SOCIETY(SocID char(6), SocName varchar(20), MentorName varchar(15),
TotalSeats int UNSIGNED, PRIMARY KEY(SocID));
create table ENROLLMENT (RollNo char(6) references STUDENT(RollNo), SID char(6),
DateOfEnrollment DATE, PRIMARY KEY(RollNo, SID), FOREIGN KEY (SID)
REFERENCES SOCIETY(SocID));
Insert multiple rows together in MYSQL
1. Retrieve names of students enrolled in any society.
2. Retrieve all society names.
3. Retrieve students' names starting with letter ‘A’.
4. Retrieve students' details studying in courses ‘computer science’ or ‘chemistry’.
-Assuming BSCHCS as Computer Science and Chemistry as BSCHCHEM
Can do the above using OR also => where course = ‘BSCHCS’ or course = ‘BSCHCHEM’
5. Retrieve students’ names whose roll no either starts with ‘X’ or ‘Z’ and ends with ‘9’
6. Find society details with more than N TotalSeats where N is to be input by the user
Stored Procedure
DELIMITER //
CREATE PROCEDURE SelectSociety(IN N INT)
BEGIN
SELECT * FROM Society WHERE TotalSeats > N;
END //
DELIMITER ;
CALL SelectSociety(5);
7. Update society table for mentor name of a specific society=> change the mentor name of
society CDF to ‘Saumya Jain’
8. Find society names in which more than five students have enrolled
9. Find the name of youngest student enrolled in society ‘NSS’
10. Find the name of most popular society (on the basis of enrolled students)
11. Find the name of two least popular societies (on the basis of enrolled students)
(If # enrolled students are equal for multiple societies, all we come in output)
Extract exactly 2 least popular:
For Society Names
Extract at least 2 popular
Added a society and an enrollment row
12. Find the student names who are not enrolled in any society
13. Find the student names enrolled in at least two societies
14. Find society names in which maximum students are enrolled
15. Find names of all students who have enrolled in any society and society names in which at
least one student has enrolled
16. Find names of students who are enrolled in any of the three societies ‘Debating’, ‘Dancing’
and ‘Sashakt’.
Consider society names as CDF, Blitz, ACM
17. Find society names such that its mentor has a name with ‘Gupta’ in it.
18. Find the society names in which the number of enrolled students is only 10% of its capacity.
19. Display the vacant seats for each society.
20. Increment Total Seats of each society by 10%
Since TotalSeats is int unsigned, after multiplication with 1.1, values are truncated.
21. Add the enrollment fees paid (‘yes’/’No’) field in the enrollment table.
22. Update date of enrollment of society id ‘s1’ to ‘2018-01-15’, ‘s2’ to current date and ‘s3’ to
‘2018-01-02’.
Take s1 as S001
Take s2 as S006
Take S3 as S007
23. Create a view to keep track of society names with the total number of students enrolled in it.
24. Find student names enrolled in all the societies.
Divide query
There are no students who are enrolled in all 4 societies. Adding record for S001
25. Count the number of societies with more than 5 students enrolled in it
Redoing above query with “more than 2 students”
26. Add column Mobile number in student table with default value ‘9999999999’
27. Find the total number of students whose age is > 20 years.
28. Find names of students who are born in 2001 and are enrolled in at least one society.
29. Count all societies whose name starts with ‘S’ and ends with ‘t’ and at least 5 students are
enrolled in the society.
Replacing “begins with S and ends with t” by “begins with B and ends with z” and 5 by 3
30. Display the following information:
Society name Mentor name Total Capacity Total Enrolled Unfilled Seats
______
_______________________________________________________
Lab Q II Do the following database administration commands:
create user, create role, grant privile ges to a role, revoke privileges from a role, create index
Select User from [Link];
CREATE a new USER
Syntax: CREATE USER 'username'@'host' IDENTIFIED BY 'password';
Example: CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Now instead of saying \connect root@localhost, I can say \connect newuser@localhost with
password ‘password’
From above point as root, I executed the following:
CREATE ROLE
Syntax: CREATE ROLE 'rolename';
Example: CREATE ROLE 'manager';
GRANT PRIVILEGES TO A ROLE
Syntax: GRANT privilege_name ON database_name.table_name TO 'rolename';
Example: GRANT SELECT ON [Link] TO 'manager';
REVOKE PRIVILEGES FROM A ROLE
Syntax: REVOKE privilege_name ON database_name.table_name FROM 'rolename';
Example: REVOKE SELECT ON [Link] FROM 'manager';
Now we can grant the privileges associated with a role to a user
Syntax: GRANT <role name> TO <user>@localhost;
Example: GRANT readonly TO newuser@localhost;
In above figure, strongdm is a DB instance
Global privileges apply to all databases on the server. Administrative privileges fall into
the global group because they enable a user to manage operations of the MySQL server
and aren't specific to a particular database.
Database privileges apply to specific databases in your MySQL instance and all of the
objects within those databases (e.g. tables, columns, and views). You can also grant
database privileges globally.
Proxy privileges allow a user to act as if they have the privileges granted to another user.
Privileges for database objects (tables, columns, stored routines, views, etc.) can apply
to all objects of one type within a particular database or to specific objects, such as a
certain table or view. You can also grant database object privileges globally.
Use the following command to see all the privileges granted:
SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES
Another Example:
The GRANT statement for the rw_user1 account grants the read and write roles, which
combine to provide the required read and write privileges.
The GRANT syntax for granting roles to an account differs from the syntax for granting
privileges: There is an ON clause to assign privileges, whereas there is no ON clause to
assign roles. Because the syntaxes are distinct, you cannot mix assigning privileges and
roles in the same statement. (It is permitted to assign both privileges and roles to an
account, but you must use separate GRANT statements, each with syntax appropriate to
what is to be granted.)
To directly GRANT privileges to a user without creating a role
CREATE INDEX
Syntax: CREATE INDEX index_name ON table_name (column1, column2, ...);
Example: CREATE INDEX idx_lastname ON mytable (lastname);
Note: Please replace 'username', 'host', 'password', 'rolename', 'privilege_name', 'database_name',
'table_name', 'index_name', 'column1', 'column2', etc. with your actual values. Also, note that the
host is typically 'localhost' if the database is on the same machine as the client. Otherwise, it
should be the IP address or domain name of the machine hosting the MySQL server.
III. Execute queries given in part I through a high-level language
using ODBC connection.
IV. Students should implement the COMPANY database schema from
Chapter 3 [1] and execute the solved queries of Chapter 7 [1].