MySQL Database Management System PDF

Day 4: DBMS - MYSQL Date: 7.11.24 1. Creating Tables: CREATE TABLE Syntax  Definition: The CREATE TABLE command is used to create a new table in the database, defining its columns and data types.  Syntax:...

Day 4: DBMS - MYSQL Date: 7.11.24 1. Creating Tables: CREATE TABLE Syntax  Definition: The CREATE TABLE command is used to create a new table in the database, defining its columns and data types.  Syntax: CREATE TABLE table_name ( column1 data_type constraints, column2 data_type constraints,...);  Example: CREATE TABLE Student ( StudentID INT PRIMARY KEY, Name VARCHAR(100), Age INT, Major VARCHAR(50)); This creates a Student table with columns StudentID, Name, Age, and Major. 2. Data Types in SQL  Definition: SQL supports various data types to specify the type of data that can be stored in each column.  Common Data Types: o INT: For whole numbers (e.g., Age INT). o VARCHAR(size): For variable-length strings, with a specified maximum length (e.g., Name VARCHAR(100)). o DATE: For date values (e.g., Birthdate DATE). o FLOAT/DOUBLE: For floating-point numbers (e.g., GPA FLOAT).  Example: CREATE TABLE Course ( CourseID INT PRIMARY KEY, CourseName VARCHAR(100), Credits INT); This example defines different data types for each column in the Course table. 3. Modifying Tables: ALTER TABLE  Definition: The ALTER TABLE command is used to modify an existing table, such as adding, modifying, or deleting columns.  Syntax: o Add a new column: ALTER TABLE table_name ADD column_name data_type; o Modify a column: ALTER TABLE table_name MODIFY column_name new_data_type; o Drop a column: ALTER TABLE table_name DROP COLUMN column_name;  Example: ALTER TABLE Student ADD Email VARCHAR(100); This adds a new Email column to the Student table. 4. Dropping Tables: DROP TABLE  Definition: The DROP TABLE command is used to permanently delete a table and all its data from the database.  Syntax: DROP TABLE table_name;  Example: DROP TABLE Course; This deletes the Course table and all its data. These SQL DDL commands are fundamental for defining and managing the structure of tables in a relational database. Home Work: 1. Write a query to select employees whose salary is between 50,000 and 100,000. 2. Write a query to select employees who do not work in the "IT" department. 3. Write a query to order the employees by their hire_date in ascending order. 4. Design an ER diagram for an employee management system with entities like Employee, Department, Project, and Task. Define relationships like WorksIn (Employee to Department), Assigned To (Employee to Project), and Performs (Employee to Task). MySQL Oueries for workbench practice : -- limit clause -- SELECT * FROM student_info LIMIT 3; SELECT * FROM student_info WHERE city_state='Tiruchirapalli' LIMIT 3; SELECT *FROM student_info LIMIT 0,10; SELECT *FROM student_info LIMIT 10; SELECT *FROM student_info LIMIT 10,20; -- from 11 to next 20 values-- SELECT *FROM student_info LIMIT 10,8; SELECT *FROM student_info LIMIT 0,1; SELECT *FROM student_info ORDER BY STUDENT_ID DESC LIMIT 1; SELECT *FROM student_info ORDER BY STUDENT_ID DESC LIMIT 0,5;