Scribd
Upload
20 views7 pages

MySQL Database and Table Creation Guide

The document provides a comprehensive guide on using MySQL commands for database management, including creating a database and tables, inserting and updating values, and executing SQL queries. It outlines specific syntax and example programs for creating a SCHOOL database, Department and Teacher tables, and various SQL operations such as displaying table details and using aggregate functions. Additionally, it includes examples of SQL queries to retrieve specific data based on conditions.

Uploaded by

alkullu.1989
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
20 views7 pages

MySQL Database and Table Creation Guide

The document provides a comprehensive guide on using MySQL commands for database management, including creating a database and tables, inserting and updating values, and executing SQL queries. It outlines specific syntax and example programs for creating a SCHOOL database, Department and Teacher tables, and various SQL operations such as displaying table details and using aggregate functions. Additionally, it includes examples of SQL queries to retrieve specific data based on conditions.

Uploaded by

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

SAMPLE

1. Creation of a database using mysql command.

AIM: Write down syntax and mysql program to create a database SCHOOL.

Syntax: CREATE DATABASE databasename;

Program:
mysql> CREATE DATABASE SCHOOL;

2. Creation of a table using mysql command.

AIM:
a. Write down a mysql program to create a table Department with following field names – Dept_ID
and Dept_Name.
b. Write down a mysql program to create a table Teacher with following field names - Teacher_ID,
First_Name, Last_Name, Gender, Salary, Date_of_Birth, Dept_No.

Syntax: CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );

Program (a):
mysql> CREATE TABLE DEPARTMENT
-> (DEPT_ID INT(2),
-> DEPT_NAME VARCHAR(30)
-> PRIMARY KEY (DEPT_ID));

Program (b):
mysql> CREATE TABLE TEACHER
-> (TEACHER_ID INT(3),
-> FIRST_NAME VARCHAR(30),
-> LAST_NAME VARCHAR(30),
-> GENDER VARCHAR(1),
-> SALARY INT(6),
-> DATE_OF_BIRTH DATE,
-> DEPT_NO INT(2),
-> PRIMARY KEY (TEACHER_ID));
SAMPLE
3. Show the database and tables created in mysql.

AIM: Write down a mysql program to show all the tables created in SCHOOL database.

Program:
mysql> Show Tables;

OUTPUT:

4. List out the field name along with its field type and constraints for a table.

AIM: Write down a mysql program to display the details of DEPARTMENT and TEACHER Tables.

Syntax: Describe tablename:

OUTPUT:
SAMPLE
5. INSERT VALUES INTO the table.

AIM: Write a mysql command to INSERT VALUES INTO table Department and Teacher.

Syntax: INSERT INTO table_name VALUES (value1, value2, value3, ...)

OUTPUT:

6. UPDATE the details of a field in a Table based on a given condition.

AIM: Write a mysql command to UPDATE the details of Salary as 55000 in Teacher table whose
Teacher_ID=101.

Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
SAMPLE
Program:
mysql> UPDATE TEACHER
-> SET SALARY=55000 WHERE TEACHER_ID=101;

OUTPUT:

7. SQL queries - Display the details of a field in a Table based on a given condition.

Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;

i. To find the names of all teachers earning more than 50000.

ii. To display Teacher_ID, First_Name,Last_Name and Dept_No of teachers who belongs to


department number 4 or 9.
SAMPLE
iii. To list all the Department numbers corresponding to departments having male teachers without
any repetition in the output.

iv. To retrieve names of all the teachers starting from letter 'A'.

v. To retrieve names of all the teachers having 6 characters in the first name and starting with 'S'.

vi. To list the names of all the Departments in the descending order of their names.
SAMPLE
vii. To retrieve the names and department numbers of all the teachers ordered by the Department
number and within each department ordered by the names of the teachers in descending order.

viii. To retrieve all the details of those employees whose last name is not specified.

8. Aggregate Functions

i. To find the maximum and minimum salary.


SAMPLE
ii. To count the number of teachers earning more than Rs 40000.

iii. To retrieve the number of teachers in “Computer Science” Department.

iv. To display Teacher name, current salary and a 10% increase in the salary for those teachers who
belongs to Department number 4.

Common questions

Powered by AI

The SQL command to find teachers with first names starting with 'A' in the 'Teacher' table is: SELECT * FROM TEACHER WHERE FIRST_NAME LIKE 'A%';

To retrieve names and department numbers of all teachers ordered by department number and then by teacher names in descending order, use: SELECT FIRST_NAME, LAST_NAME, DEPT_NO FROM TEACHER ORDER BY DEPT_NO, FIRST_NAME DESC, LAST_NAME DESC;

You can count the number of teachers earning more than Rs 40,000 using the query: SELECT COUNT(*) FROM TEACHER WHERE SALARY > 40000;

To retrieve all entries of employees whose last names are not specified, you would use: SELECT * FROM TEACHER WHERE LAST_NAME IS NULL;

The query to find the maximum and minimum salary from the 'Teacher' table is: SELECT MAX(SALARY), MIN(SALARY) FROM TEACHER;

To update the salary of a specific teacher in the 'Teacher' table based on their Teacher_ID, you can use the SQL command: UPDATE table_name SET column1 = value1 WHERE condition. Specifically for updating salary: mysql> UPDATE TEACHER SET SALARY=55000 WHERE TEACHER_ID=101;

The SQL command to create a table in a MySQL database is 'CREATE TABLE table_name (column1 datatype, column2 datatype, ...)'. To create a 'Teacher' table, the command would be: mysql> CREATE TABLE TEACHER (TEACHER_ID INT(3), FIRST_NAME VARCHAR(30), LAST_NAME VARCHAR(30), GENDER VARCHAR(1), SALARY INT(6), DATE_OF_BIRTH DATE, DEPT_NO INT(2), PRIMARY KEY (TEACHER_ID))

To increase the salary by 10% for teachers in Department number 4, use: UPDATE TEACHER SET SALARY = SALARY * 1.10 WHERE DEPT_NO = 4;

To insert a new entry into the 'Department' table, use the INSERT INTO command: INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (value1, value2); For example: INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (10, 'Mathematics')

To list all the Department numbers with male teachers and ensure no repetitions, you can use a SELECT query with DISTINCT: SELECT DISTINCT DEPT_NO FROM TEACHER WHERE GENDER='M';

You might also like