Managing Database Tables in MySQL Practical Examples
Learn practical examples of creating, managing, and manipulating tables in a MySQL database. Topics include creating a database and tables, altering table structure, populating tables with data, updating records, and more.
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
MYSQL PRACTICALS -1 Create a Database called College CREATE DATABASE COLLEGE; Create a table called Student with the following details in the College database: rno tinyint s_name character, length 10, Gender, Enum( M , F ), d_o_b, date, class, variable character, length 10, mark, tinyint. USE COLLEGE; CREATE TABLE STUDENT (RNO TINYINT, S_NAME CHAR(10), GENDER ENUM( M , F ), D_O_B DATE, CLASS VARCHAR(10), MARK INT);
View the databases existing in the system. SHOW DATABASES; View the tables existing in the database college. USE COLLEGE; SHOW TABLES; Display the structure of the student table. DESCRIBE STUDENT; Add a column called Address ( varchar, type 40) to the Student table. ALTER TABLE STUDENT ADD ADDRESS VARCHAR(40); Delete the column address from the Students table. ALTER TABLE STUDENT DROP ADDRESS ;
Change the type of the column S_Name to varchar type length 10 in the Students table. ALTER TABLE STUDENT MODIFY S_NAME VARCHAR(10) ; Change the column name mark to marks in the Student table. ALTER TABLE STUDENT CHANGE MARK MARKS INT ; Create a table called DUMMY with the following details in the college database: rno tinyint , S_Name varchar length 10 USE COLLEGE; CREATE TABLE DUMMY (RNO TINYINT, S_NAME VARCHAR(10)); Delete the table DUMMY from the database College. DROP TABLE DUMMY; Rename the table Student to Students in the database college. RENAME TABLE STUDENT TO STUDENTS;
Populate the Students table with the following data: INSERT INTO STUDENTS VALUES (5, ARUN , M , 1995-10-12 , FYBCOM ,45 ), (2, VIJAY , M , 1993-08-22 , TYBCOM ,90), (3, MEERA , F , 1995-11-06 , FYBCOM ,65 ), (4, SANJAY , M , 1994-10-15 , SYBCOM ,40 ), (1, PREETI , F , 1995-01-19 , FYBCOM ,78 ), (6, DEEPA , F , 1994-07-18 , SYBCOM ,90 ), (7, HIRAL , F , 1993-05-25 , TYBCOM ,32 ), (8, ROSHAN , M , 1994-04-21 , SYBCOM ,90 );
In the Students table change the MARKS of Hiral to 35 . UPDATE STUDENTS SET MARKS=35 WHERE S_NAME= HIRAL ; In the Students table change the NAME OF Preeti to Priti UPDATE STUDENTS SET S_NAME= PRITI WHERE S_NAME= PREETI ; In the Students table increase marks by 5 to all . UPDATE STUDENTS SET MARKS=MARKS+5;
In the Students table decrease marks by 5 to all whose marks is less than 70 . UPDATE STUDENTS SET MARKS=MARKS-5 WHERE MARKS < 70; Add a row to the students table with the following data: INSERT INTO STUDENTS VALUES (9, KATHY , F , 1995-06-06 , FYBCOM , 50 ); Remove the row with name KATHY from the students table. DELETE FROM STUDENTS WHERE S_NAME= KATHY ;
