SQL-Example

Create table student and mark in database “MyDb” with following specification

• Table student should have the five fields student_id, student_name, level, gender and contact. Use appropriate  data type and format for each field and define student_id as primary key
• Table mark should have three fields student_id, subject, and mark_obtained appropriate data types . define student_id as foreign key.
• Delete, Alter, update, read the tables

Create Database Mydb  
(Select Mydb database before creating table)

//CREATE TABLE STUDENT
CREATE TABLE student (

  student_id INT NOT NULL PRIMARY KEY,

  student_name VARCHAR(50) NOT NULL,

  level VARCHAR(20) NOT NULL,

  gender VARCHAR(10) NOT NULL,

  contact VARCHAR(20) NOT NULL

);

//create the mark table
CREATE TABLE mark (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  subject VARCHAR(50) NOT NULL,
  mark_obtained INT NOT NULL,
  FOREIGN KEY (student_id) REFERENCES student(student_id)
);

//insert data into the student table
INSERT INTO student (student_id, student_name, level, gender, contact)
VALUES
  (1, 'Ishant', 'Senior', 'Male', '123-456-7890'),
  (2, 'Sita', 'Junior', 'Female', '987-654-3210'),
  (3, 'Dilip', 'Intern', 'Male', '777-777-777');

//insert data into the mark table
INSERT INTO mark (student_id, subject, mark_obtained)
VALUES
  (1, 'Math', 85),
  (1, 'English', 92),
  (2, 'Math', 90),
  (2, 'English', 88),
  (3, 'Math', 78),
  (3, 'English', 80);

//Select all columns from the student table where the gender is 'Male':
SELECT * FROM student WHERE gender = 'Male';

//Select the student_name and mark_obtained columns from the mark table where the subject is 'Math':
SELECT student_name, mark_obtained FROM student
  JOIN mark ON student.student_id = mark.student_id
  WHERE subject = 'Math';

//Select all columns from the student table where the level is 'Senior' and the gender is 'Male':
SELECT * FROM student WHERE level = 'Senior' AND gender = 'Male';

//Select the student_name and level columns from the student table where the gender is 'Male' or the level is 'Senior':
SELECT student_name, level FROM student WHERE gender = 'Male' OR level = 'Senior';

//Add a new column named email to the student table:
ALTER TABLE student ADD COLUMN email VARCHAR(255);

//insert email data in student table in above where id =1
UPDATE student SET email = ishant@abc.com' WHERE student_id = 1;

//Delete a student record and all associated mark records:
DELETE FROM student WHERE student_id = 3;

//To delete a student record and all associated mark records:
DELETE FROM mark
 WHERE id = 1;

//To retrieve all student records from the "student" table:
SELECT * FROM student;

//To retrieve all student records from the "mark" table:
SELECT * FROM student;

//retrieve students name who scored greater than 90
(use Join as marks and students are in different table)
SELECT student_name
FROM student
JOIN mark ON student.student_id = mark.student_id
WHERE mark.mark_obtained > 90;


//Display only female
SELECT * FROM student WHERE gender = 'female';

//Display which start with i
SELECT student_name FROM student WHERE student_name LIKE 'i%';

//To update a student's contact information:
UPDATE student
SET contact = '9876543210'
WHERE student_id = 1;

//To update a student's mark in a specific subject:
UPDATE mark
SET mark_obtained = 90.0
WHERE student_id = 1 AND subject = 'Math';

//inner join
SELECT s.student_id, s.student_name, m.subject, m.mark_obtained
FROM student s
INNER JOIN mark m
ON s.student_id = m.student_id;