In this post we will learn complete MySQL like how to install it and how to create database table and how to insert record in database table and more other MySQL related task which is given below:
Installing MySQL
First download the installer package, unzip the folder and run the setup.exe file. After installation is complete everything is in
C:\mysql.Now open C:\mysql and go to the mysqld server which is probably
C:\mysql\binType "mysql"
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlyou can see as:
mysql>To show databases you can type:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.13 sec)Now you can set the username and password using below command :
mysql> mysqladmin -u root password "new_password";After setting up the password you can type the below command:
mysql> mysql -u root -p
Enter password:*******Now you can see:
mysql>To create database write below query:
CREATEDATABASE database_name;Example:
CREATEDATABASE employees; 
Now show database:
Database is created now we creating tables inside the databases:
To select the database you can type the below query:
mysql> use database_name;Example:
mysql> use employee;After this we are ready to create the mysql database:
Example
Now we discuss movie management schema with the help of some queries
DDL to create database and tables
Creating Database
CREATE DATABASE mt_db;
USE mt_db;
Creating tables
Create table CUSTOMER_MASTER
(
CUSTOMER_ID Varchar(10),
CUSTOMER_NAME Varchar(30) NOT NULL,
CONTACT_NO BIGINT(10),
CONTACT_ADD Varchar(20),
DATE_OF_REGISTRATION Date NOT NULL,
AGE Varchar(15)NOT NULL,
Constraint MT_cts1 PRIMARY KEY(CUSTOMER_ID)
);
Create table LIBRARY_CARD_MASTER
(
CARD_ID Varchar(10),
DESCRIPTION Varchar(30) NOT NULL,
AMOUNT BIGINT(50),
NUMBER_OF_YEARS bigint(10) NOT NULL,
Constraint MT_cts2 PRIMARY KEY(CARD_ID)
);
Create table MOVIES_MASTER
(
MOVIE_ID Varchar(10),
MOVIE_NAME Varchar(50) NOT NULL,
RELEASE_DATE Varchar(30) NOT NULL,
LANGUAGE Varchar(30),
RATING int(2),
DURATION VARCHAR(10) NOT NULL,
MOVIE_TYPE Varchar(3),
MOVIE_CATEGORY VARCHAR(20) NOT NULL,
DIRECTOR VARCHAR(20) NOT NULL,
LEAD_ROLE_1 Varchar(3) NOT NULL,
LEAD_ROLE_2 VARCHAR(4) NOT NULL,
RENT_COST BIGINT(10),
Constraint MT_cts4 PRIMARY KEY(MOVIE_ID)
);
Create table CUSTOMER_CARD_DETAILS
(
CUSTOMER_ID Varchar(10),
CARD_ID VARCHAR(10),
ISSUE_DATE DATE NOT NULL,
Constraint MT_cts3 PRIMARY KEY(CUSTOMER_ID),
Constraint MT_CTS41 FOREIGN KEY(CUSTOMER_ID) References CUSTOMER_MASTER(CUSTOMER_ID),
Constraint MT_CTS42 FOREIGN KEY(CARD_ID) References LIBRARY_CARD_MASTER(CARD_ID)
);
Create table CUSTOMER_ISSUE_DETAILS
(
ISSUE_ID Varchar(10) NOT NULL,
CUSTOMER_ID Varchar(10) NOT NULL,
MOVIE_ID VARCHAR(10),
ISSUE_DATE Date NOT NULL,
RETURN_DATE Date NOT NULL,
ACTUAL_DATE_RETURN Date NOT NULL,
Constraint MT_cts5 PRIMARY KEY(ISSUE_ID),
Constraint MT_Mem FOREIGN KEY(CUSTOMER_ID) References CUSTOMER_MASTER(CUSTOMER_ID),
Constraint MT_Mem1 FOREIGN KEY(MOVIE_ID) References MOVIES_MASTER(MOVIE_ID)
);DML to insert records
Insert into CUSTOMER_MASTER Values('CUS001', 'AMIT', 9876543210,'ADD1', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS002', 'ABDHUL', 8765432109,'ADD2', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS003', 'GAYAN', 7654321098,'ADD3', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS004', 'RADHA', 6543210987,'ADD4', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS005', 'GURU', NULL,'ADD5', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS006', 'MOHAN', 4321098765,'ADD6', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS007', 'NAME7', 3210987654,'ADD7', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS008', 'NAME8', 2109876543,'ADD8', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS009', 'NAME9', NULL,'ADD9', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS010', 'NAM10', 9934567890,'ADD10', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS011', 'NAM11', 9875678910,'ADD11', '2013-02-12', '21');
Insert into LIBRARY_CARD_MASTER Values('CR001', 'DES1', 200, 5);
Insert into LIBRARY_CARD_MASTER Values('CR002', 'DES2', 400, 9);
Insert into LIBRARY_CARD_MASTER Values('CR003', 'DES3', 600, 8);
Insert into LIBRARY_CARD_MASTER Values('CR004', 'DES4', 800, 7);
Insert into LIBRARY_CARD_MASTER Values('CR005', 'DES5', 1200, 6);
Insert into MOVIES_MASTER Values('MV001', 'DIEHARD', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','ACTION','DIR1','L1','L2',100);
Insert into MOVIES_MASTER Values('MV002', 'THE MATRIX', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ACTION','DIR2','L1','L2',100);
Insert into MOVIES_MASTER Values('MV003', 'INCEPTION', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','ACTION','DIR3','L1','L2',100);
Insert into MOVIES_MASTER Values('MV004', 'DARK KNIGHT', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ACTION','DIR4','L1','L2',100);
Insert into MOVIES_MASTER Values('MV005', 'OFFICE S', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR5','L1','L2',100);
Insert into MOVIES_MASTER Values('MV006', 'SHAWN OF DEAD', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR6','L1','L2',100);
Insert into MOVIES_MASTER Values('MV007', 'YOUNG FRANKEN', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR7','L1','L2',100);
Insert into MOVIES_MASTER Values('MV008', 'CAS', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR8','L1','L2',100);
Insert into MOVIES_MASTER Values('MV009', 'GWW', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR9','L1','L2',100);
Insert into MOVIES_MASTER Values('MV010', 'TITANIC', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR10','L1','L2',100);
Insert into MOVIES_MASTER Values('MV011', 'THE NOTE BOOK', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR11','L1','L2',100);
Insert into CUSTOMER_CARD_DETAILS Values('CUS001', 'CR001', '2012-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS002', 'CR002', '2012-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS003', 'CR002', '2013-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS004', 'CR003', '2013-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS005', 'CR003', '2012-05-13');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS001', 'CUS001', 'MV001', '2012-05-13', '2012-05-13','2012-05-13');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS002', 'CUS001', 'MV001', '2012-05-01', '2012-05-16','2012-05-16');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS003', 'CUS002', 'MV004', '2012-05-02', '2012-05-06','2012-05-16');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS004', 'CUS002', 'MV004', '2012-04-03', '2012-04-16','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS005', 'CUS002', 'MV009', '2012-04-04', '2012-04-16','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS006', 'CUS003', 'MV002', '2012-03-30', '2012-04-15','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS007', 'CUS003', 'MV003', '2012-04-20', '2012-05-05','2012-05-05');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS008', 'CUS003', 'MV005', '2012-04-21', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS009', 'CUS003', 'MV001', '2012-04-22', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS010', 'CUS003', 'MV009', '2012-04-22', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS011', 'CUS003', 'MV010', '2012-04-23', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS012', 'CUS003', 'MV010', '2012-04-24', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS013', 'CUS003', 'MV008', '2012-04-25', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS014', 'CUS004', 'MV007', '2012-04-26', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS015', 'CUS004', 'MV006', '2012-04-27', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS016', 'CUS004', 'MV006', '2012-04-28', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS017', 'CUS004', 'MV001', '2012-04-29', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS018', 'CUS010', 'MV008', '2012-04-24', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS019', 'CUS011', 'MV009', '2012-04-27', '2012-05-07','2012-05-25');
Queries and their solutions
1.count the members who has gold cards
ans:select count(customer_id) from customer_card_details where card_id
in
(select card_id from library_card_master where description='gold
card');2.display the name of member who issued movie and the count of the
movies issued and display 0 for the member who have not issued any
movie
ans:select customer_name,count(movie_id) count
from
customer_issue_details a,customer_master b
where
a.customer_id=b.customer_id
group by b.customer_id union
select
customer_name,0 as count
from customer_master
where customer_id not in
(select customer_id from customer_issue_details);3.display the name of the person starting with letter 'r' and category
is 'comedy'
ans:
select distinct a.customer_name
from customer_master
a,customer_issue_details b,movies_master c
where
a.customer_id=b.customer_id and b.movie_id=c.movie_id and
c.movie_category='comedy'
and a.customer_name like 'r%';4.display id,name & total rent of customers for movie issued
ans:select a.customer_id,customer_name,count(a.movie_id)*c.rent_cost
rent
from customer_issue_details a,customer_master b ,(select
movie_id,rent_cost from movies_master) c
where
a.customer_id=b.customer_id and a.movie_id=c.movie_id
group by
b.customer_id;
5.display id,name,card id,amount in $(amount/54.42) upto 0 decimals
ans:
select a.customer_id,customer_name,b.card_id,round(amount/54.42)
Amount
from customer_master a,customer_card_details
b,library_card_master c
where a.customer_id=b.customer_id and
b.card_id=c.card_id;6.display id,name of customers who dont have library card but still
have issued the movie
ans:
select distinct b.customer_id,customer_name
from
customer_issue_details a,customer_master b
where
a.customer_id=b.customer_id
and a.customer_id not in (select
customer_id from customer_card_details);7.display the no.of customers with first letter 'r' and have paid fine
i.e actual return date is greater than return date
ans:
select count(b.customer_id) count from (select customer_id
from
customer_issue_details
where return_date>actual_date_return
group by
customer_id) b,customer_master c
where b.customer_id=c.customer_id and
c.customer_name like 'r%';
8.display customer name,customer id who have issued max and min no.of
movies issued
ans:
select customer_name,a.customer_id
from customer_issue_details
a,customer_master b
where a.customer_id=b.customer_id
group by
a.customer_id
having count(a.movie_id)=(
select min(a.count) min from
(select customer_id,count(movie_id) count
from customer_issue_details
group by customer_id) a)
or count(a.movie_id)=(
select max(b.count) min
from (select customer_id,count(movie_id) count
from
customer_issue_details
group by customer_id) b);9.display id,name,mobile num and description of all customers.if
mobile num is not available then display address as alias contact,for
those who does't have library cards display null as description
ans:
select a.customer_id,a.customer_name,coalesce
(a.contact_no,a.contact_add) contact,description
from customer_master
a,library_card_master b,customer_card_details c
where
a.customer_id=c.customer_id and c.card_id=b.card_id union
select
a.customer_id,a.customer_name,coalesce(a.contact_no,a.contact_add)
contact,null as description
from customer_master a
where customer_id not
in (select customer_id from customer_card_details) ;
10.display customer details and movie id for those who issued same
movie more than one time OR
//dislay customer details who watched same movie more than once
ans:select m.customer_id,customer_name,movie_id,count(i.movie_id) as
count_of_movie
from customer_master m join customer_issue_details i
on m.customer_id = i.customer_id
group by movie_id
having count(movie_id)>1;
11.display customer information those who has library cards
ans:
select a.customer_id,customer_name
from customer_card_details
a,customer_master b,library_card_master c
where
a.customer_id=b.customer_id and a.card_id=c.card_id;12.display the members who watch the movie but doesnt have card
ans:select distinct b.customer_id,customer_name
from
customer_issue_details a,customer_master b
where
a.customer_id=b.customer_id
and a.customer_id not in (select
customer_id from customer_card_details);13.display sr no as 2 digits of issue id,emp id,movie watched,video id
and sort by sr no
ans:
select substring(issue_id,4) sr_no,issue_id as
video_id,customer_id,movie_id
from customer_issue_details order by
sr_no;
14.display total revenue spent on videos by each customer
ans:select a.customer_id,count(a.movie_id)*c.rent_cost rent
from
customer_issue_details a,customer_master b ,(select
movie_id,rent_cost
from movies_master) c
where a.customer_id=b.customer_id and
a.movie_id=c.movie_id
group by b.customer_id union
select customer_id,0
as rent
from customer_master
where customer_id not in(select
customer_id from customer_issue_details);
15.display customer name in perfect order
i.e 1st letter in ucase remaining lcase
ans:
select concat(ucase(substring(customer_name,1,1)),lcase(substring
(customer_name,2,5)) )
from customer_master;16.count how many times a movie issued and arrange them in desc order
and display 0 for the movie not issued
ans:
select * from (select a.movie_id,count(b.movie_id) count
from
movies_master a,customer_issue_details b
where a.movie_id=b.movie_id
group by a.movie_id union
select movie_id ,0 as count from
movies_master
where movie_id not in (select movie_id from
customer_issue_details)) x order by movie_id desc;17.waq to display cus id and cus name and address as if phone num
presents display phone num otherwise address.
SELECT CUSTOMER_ID,CUSTOMER_NAME,coalesce(CAST(CONTACT_NO AS
CHAR),CAST(CONTACT_ADD AS CHAR))
from customer_master;
SELECT CUSTOMER_ID,CUSTOMER_NAME,(CASE
WHEN CAST(CONTACT_NO AS CHAR) IS
NULL THEN CAST(CONTACT_ADD AS CHAR)
ELSE CAST(CONTACT_NO AS CHAR)
END) AS CONTACT
FROM CUSTOMER_MASTER;
18.waq that num of customers registered in 2012 year and provided
contact num
use NO_OF_CUSTOMERS as alias name.
ANS:SELECT COUNT(*) AS NO_OF_CUSTOMERS
FROM CUSTOMER_MASTER
WHERE DATE_OF_REGISTRATION LIKE '%2012%' AND CONTACT_NO IS NOT NULL;19.display customer id, cus name,year of registration,library card
id,card issue date
alias name registered_year for year of registration.
ANS:SELECT seLect C.CUSTOMER_ID,C.CUSTOMER_NAME,year
(C.DATE_OF_REGISTRATION) REGISTERED_YEAR,L.CARD_ID,D.ISSUE_DATE
FROM
CUSTOMER_MASTER C,LIBRARY_CARD_MASTER L,CUSTOMER_CARD_DETAILS D
WHERE
C.CUSTOMER_ID=D.CUSTOMER_ID AND L.CARD_ID=D.CARD_ID;
20. (***REPEATED)waq to display movie name and num of times movie issued to
customers..in case of no movie issued
to customers display 0.. use alias name as NO_OF_TIMES
SELECT M.MOVIE_NAME,COUNT(*) AS NO_OF_TIMES
FROM CUSTOMER_ISSUE_DETAILS D,MOVIES_MASTER M,CUSTOMER_MASTER C
WHERE M.MOVIE_ID=D.MOVIE_ID AND D.CUSTOMER_ID=C.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
union
SELECT MOVIE_NAME,0
FROM MOVIES_MASTER
WHERE MOVIE_NAME NOT IN (SELECT M.MOVIE_NAME
FROM CUSTOMER_ISSUE_DETAILS D,MOVIES_MASTER M,CUSTOMER_MASTER
C
WHERE M.MOVIE_ID=D.MOVIE_ID AND D.CUSTOMER_ID=C.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID);
21.waq to display customer id and customer name ,num of times movie
issued to customer in comedy movie category
display only customers who has issude more than once
ANS:
SELECT M.CUSTOMER_NAME,M.CUSTOMER_ID,P.NO_OF_TIMES
FROM CUSTOMER_MASTER M,
(SELECT I.CUSTOMER_ID,COUNT(*) AS NO_OF_TIMES
FROM CUSTOMER_ISSUE_DETAILS I,(
SELECT MOVIE_ID,MOVIE_CATEGORY
FROM MOVIES_MASTER
WHERE MOVIE_CATEGORY='COMEDY')T
WHERE I.MOVIE_ID=T.MOVIE_ID
GROUP BY I.CUSTOMER_ID
HAVING COUNT(*)>1)P
WHERE M.CUSTOMER_ID=P.CUSTOMER_ID;22.(***REPEATED)waq to display customerid and total rent paid by them.
use alias name as total_cost.
ANS:select * from movies_master;
select c.customer_id,sum(rent_cost) as total_cost
from customer_master c,customer_issue_details d,movies_master m
where c.customer_id=d.customer_id and m.movie_id=d.movie_id
group by customer_id;
23.waq to display customerid,cusname,contactno,num of movies issued to
customer based on category and category
display the customer who has issude for more than one movie from that
caregory.
display phone num as "+91-987-654-3210".
ANS:select m.customer_id,customer_name,
concat('+91-',substring(contact_no,1,3),'-',substring
(contact_no,4,3),'-',substring(contact_no,7) )as contact_no,
count(i.movie_id) as no_of_movies,movie_category
from customer_master m join customer_issue_details i join
movies_master mv
on m.customer_id = i.customer_id and i.movie_id = mv.movie_id
group by m.customer_id
having count(*) >1;
Thanks for reading our blogs i hope it may help you to understand the concept of MySQL,
If you need any help related to MySQL Assignment then you can send your quote at here:
Comments