top of page
Search

MySQL Assignment Help | Database Assignment Help | Realcode4you



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\bin

Type "mysql"


C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql

you 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:

realcode4you@gmail.com

335 views0 comments
bottom of page