DDL(Data Definition Language) Commands.

1. 1. Create a table named STUDENT having the following fields.

   Student(Student_id, name, age, gender, courses, maks, city)

   CREATE TABLE student (

     student_id INT PRIMARY KEY,

     name VARCHAR(50),

     age INT,

     gender CHAR(1),

     course VARCHAR(30),

     marks INT,

     city VARCHAR(30)

   );

   INSERT INTO (DML Commands)

   2. Insert one record into the student table.

   INSERT INTO student VALUES (1, ‘Amit’, 20, ‘M’, ‘BCA’, 85, ‘Kolkata’);

   3. Insert another student record.

   INSERT INTO student VALUES (2, ‘Riya’, 19, ‘F’, ‘BCA’, 78, ‘Delhi’);

   4. Insert multiple records.

   INSERT INTO student VALUES

   (3, ‘Suman’, 21, ‘M’, ‘BSc’, 67, ‘Mumbai’),

   (4, ‘Neha’, 20, ‘F’, ‘BCA’, 90, ‘Kolkata’);

   5. Insert data without marks.

   INSERT INTO student (student_id, name, age, gender, course, city)

   VALUES (5, ‘Rahul’, 22, ‘M’, ‘BSc’, ‘Delhi’);

    

   6. Insert 5 more records into the student table.

   INSERT INTO student VALUES (6,’Pooja’,19,’F’,’BCom’,72,’Mumbai’);

   INSERT INTO student VALUES (7,’Arjun’,21,’M’,’BCom’,88,’Kolkata’);

   INSERT INTO student VALUES (8,’Sneha’,20,’F’,’BCA’,60,’Delhi’);

   INSERT INTO student VALUES (9,’Vikas’,23,’M’,’BSc’,49,’Mumbai’);

   INSERT INTO student VALUES (10,’Anita’,21,’F’,’BCom’,95,’Kolkata’);

   INSERT INTO student VALUES (2,’Tina’,20,’F’,’BCom’,88,’Delhi’);

    

   ALTER TABLE & DROP 
   

   7. Add email column.

   ALTER TABLE student ADD email VARCHAR(50);

   8. Add phone number.

   ALTER TABLE student ADD phone BIGINT;

   9. Modify name length.

   ALTER TABLE student MODIFY name VARCHAR(100);

   10. Modify the marks datatype.

   ALTER TABLE student MODIFY marks FLOAT;

   11. Drop phone column.

   ALTER TABLE student DROP phone;

   12. Add a semester column.

   ALTER TABLE student ADD semester INT;

   13. Add an admission_year column.

   ALTER TABLE student ADD admission_year INT;

   14. Modify city column size 30 to 50.

   ALTER TABLE student MODIFY city VARCHAR(50);

   15. Drop email column.

   ALTER TABLE student DROP email;

   16. Add address column.

   ALTER TABLE student ADD address VARCHAR(100);

   17. Drop address column.

   ALTER TABLE student DROP address;

   18. Modify course column size (30 to 40)

   ALTER TABLE student MODIFY course VARCHAR(40);

   19. Add dob column.

   ALTER TABLE student ADD dob DATE;

   20. Drop column dob;

   ALTER TABLE student DROP dob;

   21. Add a scholarship column.

   ALTER TABLE student ADD scholarship BOOLEAN;

   CREATE VIEW & ALTER VIEW & DROP VIEW

2. What is a VIEW in SQL?

   A VIEW in SQL is a virtual table that is created using a SELECT query.
   It does not store data physically; instead, it shows data from one or more tables.

3. Below is the standard structure (syntax) of VIEW commands.
4.  
5. CREATE VIEW view_name AS
   SELECT column1, column2, …
   FROM table_name
   WHERE condition;
6.  

7. 22. Create a view showing only passed students (marks ≥ 40)

   CREATE VIEW passed_students AS

   SELECT student_id, name, marks

   FROM students

   WHERE marks >= 40;

   Or

   CREATE OR REPLACE VIEW passed_students AS

   SELECT student_id, name, marks

   FROM students

   WHERE marks >= 50;

8. 23.  Create a view with selected columns

   CREATE VIEW student_basic_info AS

   SELECT student_id, name

   FROM students;

9. 24. Modify an existing View.

10. ALTER VIEW passed_students AS

    SELECT student_id, name,

    FROM students

    WHERE marks >= 50;

     25. Drop a view Passed_Student.

    DROP VIEW passed_students;

     26. Drop view only if it exists.

    DROP VIEW IF EXISTS passed_students;

    TRUNCATE 

The TRUNCATE command is used to remove all records (rows) from a table very quickly.

Syntex:

TRUNCATE TABLE table_name;

TRUNCATE vs DELETE (Very Important for Exams)

27. Removes all rows from the Student table.

TRUNCATE TABLE Student;

RENAME

1. The RENAME command is used to change the name of a database object, mainly tables (and sometimes columns, depending on DBMS).

Syntax (Structure):

RENAME TABLE old_table_name TO new_table_name;

28: Rename the Table Students to Student_info.

RENAME TABLE students TO student_info;