MYSQL PRACTICAL - 3
undefined
Display the names of all students in uppercase.
SELECT UPPER(S_NAME)
FROM STUDENTS;
Display the names of all students in lowercase in alphabetical order of names.
SELECT LOWER(S_NAME)
FROM STUDENTS
ORDER BY S_NAME;
Display the names of all students in lowercase in descending order of names.
SELECT LOWER(S_NAME)
FROM STUDENTS
ORDER BY S_NAME DESC;
Display the names of all students in first letter in uppercase and other letters in lower
case
.
SELECT INITCAP(S_NAME)
FROM STUDENTS;
Display the names of all students names followed by ‘has scored’ followed by their
respective marks.
SELECT CONCAT(S_NAME, ‘has scored ’ , MARKS)
FROM STUDENTS;
Display 3 characters starting from 2 place from the names of all students.
SELECT MID(S_NAME, 2,3)
FROM STUDENTS;
Display 2 characters starting from 3 place from the names of all students.
SELECT MID(S_NAME, 3,2)
FROM STUDENTS;
Display length of the names of all students.
SELECT LENGTH(S_NAME)
FROM STUDENTS;
Display the names of all students in reverse.
SELECT REVERSE(S_NAME)
FROM STUDENTS;
Display the names of all students after removing the leading and trailing blank spaces.
SELECT TRIM(S_NAME)
FROM STUDENTS;
Display the names of all students after removing the leading blank spaces.
SELECT LTRIM(S_NAME)
FROM STUDENTS;
Display the names of all students after removing the trailing blank spaces.
SELECT RTRIM(S_NAME)
FROM STUDENTS;
Display the names and marks of all students padding the marks with *.before the
marks
SELECT S_NAME, LPAD(MARKS,3,*)
FROM STUDENTS;
Display the names and marks of all students padding the marks with * after the
marks
SELECT S_NAME, RPAD(MARKS,3,*)
FROM STUDENTS;
Return a number without its negative sign
SELECT ABS(-6);
Return a value of 3
2
SELECT POW(3,2);
Return the remainder of division of 1000 by 400
SELECT MOD(1000,400);
Return the rounded value of 76.873 to its nearest integer
SELECT ROUND(76.873,0);
Return the rounded value of 76.873 to one decimal place
SELECT ROUND(76.873,1);
Return the rounded value of 76.873 to two decimal places
SELECT ROUND(76.873,2);
Return the square root of 126736
SELECT SQRT(126736);
Display the system date
SELECT NOW();
Display the names of all students and date from their date of birth
SELECT S_NAME,DATE(D_O_B)
FROM STUDENTS;
Display the names of all students and day of the month from their date
of birth
SELECT S_NAME,DAY(D_O_B)
FROM STUDENTS;
Display the names of all students and month from their date of birth
SELECT S_NAME,MONTH(D_O_B)
FROM STUDENTS;
Display the names of all students and name of the weekday
of their date of birth
SELECT S_NAME,DAYNAME(D_O_B)
FROM STUDENTS;
Display the names of all students and name of the month of their date
of birth
SELECT S_NAME,MONTHNAME(D_O_B)
FROM STUDENTS;
Display the names of all students and year from their date of birth
SELECT S_NAME,YEAR(D_O_B)
FROM STUDENTS;
Display the total number of students/rows in the STUDENTS table.
SELECT COUNT(*)
FROM STUDENTS;
Display the class & number of students in each class.
SELECT CLASS, COUNT(*)
FROM STUDENTS
GROUP BY CLASS;
Display the class & number of students who got more than 60 marks
in each class.
SELECT CLASS, COUNT(*)
FROM STUDENTS
WHERE MARKS>60
GROUP BY CLASS;
Display the sum of marks got by students.
SELECT SUM(MARKS)
FROM STUDENTS;
Display the class & sum of marks got by each class.
SELECT CLASS, SUM(MARKS)
FROM STUDENTS
GROUP BY CLASS ;
Display the class & average marks got by each class.
SELECT CLASS, AVG(MARKS)
FROM STUDENTS
GROUP BY CLASS ;
Display the class & minimum marks scored in each class.
SELECT CLASS, MIN(MARKS)
FROM STUDENTS
GROUP BY CLASS ;
Display the class & maximum marks scored in each class.
SELECT CLASS, MAX(MARKS)
FROM STUDENTS
GROUP BY CLASS ;
Display the class & marks scored by the class whose average marks is
greater than 70.
SELECT CLASS, MARKS
FROM STUDENTS
GROUP BY CLASS
HAVING AVG(MARKS)>70;
Display the minimum marks scored by the students.
SELECT MIN(MARKS)
FROM STUDENTS;
Display the class & minimum marks scored in each class.
SELECT CLASS, MIN(MARKS)
FROM STUDENTS
GROUP BY CLASS ;
Display the maximum marks scored by the students.
SELECT MAX(MARKS)
FROM STUDENTS;
Display the class & maximum marks scored in each class.
SELECT CLASS, MAX(MARKS)
FROM STUDENTS
GROUP BY CLASS ;
Display the roll no & marks of students who got more than the
average marks of all students.
SELECT RNO, MARKS
FROM STUDENTS
WHERE MARKS> ( SELECT AVG(MARKS)
FROM STUDENTS) ;
Display the roll no & marks of all students who got same as roll no
2 .
SELECT RNO, MARKS
FROM STUDENTS
WHERE MARKS = ( SELECT MARKS
FROM STUDENTS
WHERE RNO =2) ;
Display the roll no and marks of all students who scored lesser
marks than as roll no 1
SELECT RNO, MARKS
FROM STUDENTS
WHERE MARKS < ( SELECT MARKS
FROM STUDENTS
WHERE RNO =1) ;
Display the roll no and marks of all students who scored lesser marks
than the average marks of SYBCOM class .
SELECT RNO, MARKS
FROM STUDENTS
WHERE MARKS < ( SELECT AVG(MARKS)
FROM STUDENTS
WHERE CLASS=‘SYBCOM’
GROUP BY CLASS) ;
This content provides practical insights and guidance on effectively managing a MySQL database. From essential concepts to advanced techniques, it covers a range of topics to enhance your database skills. Whether you are a beginner or an experienced user, this guide offers valuable information to optimize database performance and streamline operations. Dive into the world of MySQL with this comprehensive resource.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Display the names of all students in uppercase. SELECT UPPER(S_NAME) FROM STUDENTS; Display the names of all students in lowercase in alphabetical order of names. SELECT LOWER(S_NAME) FROM STUDENTS ORDER BY S_NAME; Display the names of all students in lowercase in descending order of names. SELECT LOWER(S_NAME) FROM STUDENTS ORDER BY S_NAME DESC;
Display the names of all students in first letter in uppercase and other letters in lower case. SELECT INITCAP(S_NAME) FROM STUDENTS; Display the names of all students names followed by has scored followed by their respective marks. SELECT CONCAT(S_NAME, has scored , MARKS) FROM STUDENTS; Display 3 characters starting from 2 place from the names of all students. SELECT MID(S_NAME, 2,3) FROM STUDENTS; Display 2 characters starting from 3 place from the names of all students. SELECT MID(S_NAME, 3,2) FROM STUDENTS; Display length of the names of all students. SELECT LENGTH(S_NAME) FROM STUDENTS;
Display the names of all students in reverse. SELECT REVERSE(S_NAME) FROM STUDENTS; Display the names of all students after removing the leading and trailing blank spaces. SELECT TRIM(S_NAME) FROM STUDENTS; Display the names of all students after removing the leading blank spaces. SELECT LTRIM(S_NAME) FROM STUDENTS; Display the names of all students after removing the trailing blank spaces. SELECT RTRIM(S_NAME) FROM STUDENTS; Display the names and marks of all students padding the marks with *.before the marks SELECT S_NAME, LPAD(MARKS,3,*) FROM STUDENTS; Display the names and marks of all students padding the marks with * after the marks SELECT S_NAME, RPAD(MARKS,3,*) FROM STUDENTS;
Return a number without its negative sign SELECT ABS(-6); Return a value of 32 SELECT POW(3,2); Return the remainder of division of 1000 by 400 SELECT MOD(1000,400); Return the rounded value of 76.873 to its nearest integer SELECT ROUND(76.873,0); Return the rounded value of 76.873 to one decimal place SELECT ROUND(76.873,1); Return the rounded value of 76.873 to two decimal places SELECT ROUND(76.873,2); Return the square root of 126736 SELECT SQRT(126736);
Display the system date SELECT NOW(); Display the names of all students and date from their date of birth SELECT S_NAME,DATE(D_O_B) FROM STUDENTS; Display the names of all students and day of the month from their date of birth SELECT S_NAME,DAY(D_O_B) FROM STUDENTS; Display the names of all students and month from their date of birth SELECT S_NAME,MONTH(D_O_B) FROM STUDENTS;
Display the names of all students and name of the weekday of their date of birth SELECT S_NAME,DAYNAME(D_O_B) FROM STUDENTS; Display the names of all students and name of the month of their date of birth SELECT S_NAME,MONTHNAME(D_O_B) FROM STUDENTS; Display the names of all students and year from their date of birth SELECT S_NAME,YEAR(D_O_B) FROM STUDENTS;
Display the total number of students/rows in the STUDENTS table. SELECT COUNT(*) FROM STUDENTS; Display the class & number of students in each class. SELECT CLASS, COUNT(*) FROM STUDENTS GROUP BY CLASS; Display the class & number of students who got more than 60 marks in each class. SELECT CLASS, COUNT(*) FROM STUDENTS WHERE MARKS>60 GROUP BY CLASS;
Display the sum of marks got by students. SELECT SUM(MARKS) FROM STUDENTS; Display the class & sum of marks got by each class. SELECT CLASS, SUM(MARKS) FROM STUDENTS GROUP BY CLASS ; Display the class & average marks got by each class. SELECT CLASS, AVG(MARKS) FROM STUDENTS GROUP BY CLASS ;
Display the class & minimum marks scored in each class. SELECT CLASS, MIN(MARKS) FROM STUDENTS GROUP BY CLASS ; Display the class & maximum marks scored in each class. SELECT CLASS, MAX(MARKS) FROM STUDENTS GROUP BY CLASS ; Display the class & marks scored by the class whose average marks is greater than 70. SELECT CLASS, MARKS FROM STUDENTS GROUP BY CLASS HAVING AVG(MARKS)>70;
Display the minimum marks scored by the students. SELECT MIN(MARKS) FROM STUDENTS; Display the class & minimum marks scored in each class. SELECT CLASS, MIN(MARKS) FROM STUDENTS GROUP BY CLASS ; Display the maximum marks scored by the students. SELECT MAX(MARKS) FROM STUDENTS; Display the class & maximum marks scored in each class. SELECT CLASS, MAX(MARKS) FROM STUDENTS GROUP BY CLASS ;
Display the roll no & marks of students who got more than the average marks of all students. SELECT RNO, MARKS FROM STUDENTS WHERE MARKS> ( SELECT AVG(MARKS) FROM STUDENTS) ; Display the roll no & marks of all students who got same as roll no 2 . SELECT RNO, MARKS FROM STUDENTS WHERE MARKS = ( SELECT MARKS FROM STUDENTS WHERE RNO =2) ;
Display the roll no and marks of all students who scored lesser marks than as roll no 1 SELECT RNO, MARKS FROM STUDENTS WHERE MARKS < ( SELECT MARKS FROM STUDENTS WHERE RNO =1) ; Display the roll no and marks of all students who scored lesser marks than the average marks of SYBCOM class . SELECT RNO, MARKS FROM STUDENTS WHERE MARKS < ( SELECT AVG(MARKS) FROM STUDENTS WHERE CLASS= SYBCOM GROUP BY CLASS) ;
