MySQL Practical File PDF

1. Problem statement: Create a student table with the student id, name, and marks as attributes where the student id is the primary key. Solution: Source Code: create table student ( -> studid int primary key, -> name varchar(30), -> marks int -> ); 2. Problem statement: In the table ‘student’ crea...

1. Problem statement: Create a student table with the student id, name, and marks as attributes where the student id is the primary key. Solution: Source Code: create table student ( -> studid int primary key, -> name varchar(30), -> marks int -> ); 2. Problem statement: In the table ‘student’ created in practical 26, insert the details of new students. Solution: Source Code: insert into student values(1, 'sanjay', 67); mysql> insert into student values(2, 'surendra', 88); mysql> insert into student values(3, 'Jamil', 74); mysql> insert into student values(4, 'Rahul', 92); mysql> insert into student values(5, 'Prakash', 78); 3.Problem statement: Write SQL command to get the details of the students with marks more than 80. Solution: Source Code: select * from student where marks >=80; PRACTICAL-4 5. Problem statement: Find the total number of customers from each country in the table (customer ID, customer Name, country) using group by. Solution: Source Code: select country, count(cname) as 'Total_Customers' from customer group by country; 6.Problem statement: Write a SQL query to order the (student ID, marks) table in descending order of the marks. Solution: Source Code: select * from student Order By name DESC; PRACTICAL 7 8. Problem statement: for the given table ‘Hospital’ write SQL command to Display patient name in upper case with year of admission. Solution: Source Code: Select UPPER(pname) as ‘patient name’, YEAR(admitdate) as ‘admit year’ From hospital; 9. Problem statement: for the given table ‘Hospital’ Create sql query to display first four letters of the patient name along with length of their name who admitted before may. Solution: Source Code: Select LEFT(pname,4) as ‘pname’, length(pname) as ‘length’ From hospital Where month(admitdate) < 5; Practical-10 WRITE ANSWERS TO QUESTIONS GIVEN BELOW:- A) WRITE A COMMAND TO CREATE DATABASE B) WRITE A COMMAND TO CREATE ABOVE TABLE C) WRITE A COMMAND TO INSERT DATA VALUES TO ABOVE TABLE D) DISPLAY COACHNAME WHOSE SPORTS IS SWIMMING IN THE TABLE CLUB. E) TO DISPLAY THE DETAILS OF THE COACHES HAVING PAY GREATER THAN 3500 IN THE ASCENDING ORDER OF NAMES IN THE TABLE CLUB. E) DISPLAY INFORMATION OF COACHES WHOSE AGE IS 34 IN THE TABLE CLUB.