Scribd
Upload
57 views

Mysql

The document provides 10 MySQL programs and queries to create and manipulate a student and teacher database. It includes commands to create and drop databases and tables, insert, update, and select data from tables, and use aggregate functions to calculate values from the tables.

Uploaded by

Harshit
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
57 views

Mysql

The document provides 10 MySQL programs and queries to create and manipulate a student and teacher database. It includes commands to create and drop databases and tables, insert, update, and select data from tables, and use aggregate functions to calculate values from the tables.

Uploaded by

Harshit
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

1. Write down a syntax and MySQL peogram to create a database STUDENT and its OUTPUT.

SYNTAX:

PROGRAM:
mysql> create database student;

OUTPUT:

2. Write down syntax and MySQL program to delete a database STUDENT and its output.

SYNTAX:

PROGRAM:
mysql> drop database student;

OUTPUT:
3. Write down a MySQL command to create a TEACHER with the following field names given
below:
 Teacher_ID,
 First_Name,
 Last_Name,
 Gender,
 Salary,
 Date_of_Birth,
 Dept_No,

PROGRAM:

mysql> CREATE TABLE Teacher

→ (
→ Teacher_ID INTEGER,
→ First_Name VARCHAR(10),→
→ Last_Name VARCHAR(10),
→ Gender CHAR(1),
→ Salary DECIMAL(10,2),
→ Date_of_Birth DATE,
→ Dept_No INTEGER
→ );

OUTPUT:

4. Write MySQL program to show the databases and Tables created.


PROGRAM:
mysql> show databases;

OUTPUT:

5. Write a MySQL program to list out the field name Teacher_ID, First Name, Last Name,
Gender, Salary, Date of Birth, Dept No along with its field type and constraints for the table
TEACHER in the database TEACHER.

PROGRAM:
mysql> use teacher;
database changed
mysql> desc teacher;

OUTPUT:
6. Write a MySQL command to INSERT VALUES INTO table TEACHER in its field Teacher_ID,
First Name, Last Name, Gender, Salary, Date of Birth, Dept No.

PROGRAM:
mysql> INSERT INTO Teacher(Teacher_ID, First_Name, Last_Name, Gender, Salary,
Date_of_Birth, Dept_No) VALUES(101, “Shanaya”, “Batra”, ‘F’, 50000, ‘1999-02-26’, 1);

OUTPUT:

7. Write a MySQL command to UPDATE the details of Salary as 55000 in teacher table whose
Teacher_ID=101.

PROGRAM:
mysql> UPDATE Teacher SET Salary = 55000 WHERE Teacher_ID=101

OUTPUT:
8. Write down MySQL program to set a default value for the field SALARY as 30000 to the table
name TEACHER.

PROGRAM:
mysql> desc teacher;
mysql> ALTER TABLE TEACHER ALTER SALARY SET DEFAULT 30000;

OUTPUT:

9. Write a MySQL command to display the details of a teacher whose Teacher_ID=101.

PROGRAM:
mysql> SELECT * FROM TEACHER WHERE Teacher_ID=101;

OUTPUT:
10. Write a MySQL command to find the following AGGREGATE FUNTIONS in Teacher table.

1) Calculate the sum of salary given to the teachers.


2) Calculate the maximum and minimum salary of the teacher.
3) Calculate the total number of teacher whose salary is more then 40000.

PROGRAM:
mysql> SELECT SUM(Salary) AS Total_Salary FROM Teacher;

OUTPUT:

PRORGRAM:
mysql> SELECT MAX(Salary) AS Max_Salary, MIN(Salary) AS Min_Salary FROM Teacher;

OUTPUT:
PROGRAM:
mysql> SELECT COUNT(Salary) FROM Teacher WHERE Salary >40000;

OUTPUT:

You might also like