In class SQL Commands Pt 1

Week 2

create a database

CREATE DATABASE demo_name;

see the databases on the server

SHOW DATABASES;

the demo_name appears along with MySQL’s four system databases: information_schema, mysql, performance_schema, sys

select the databased to work with

USE demo_name;

response is Database changed.

Create a simple table.

How will the table look?

CREATE TABLE students (

student_id INT,

name VARCHAR(50),

major VARCHAR(50)

);

The table will have columns but no rows or PK

See the tables in the database

SHOW TABLES;

students appear in the list of tables

See the structure of the table

DESCRIBE students;

MySQL displays the columns, data types, whether NULL is allowed, and key information. PK not defined so Key is empty

Delete the table

DROP TABLE students;

Verify that the table is gone. What difference does this show?

SHOW TABLES;

this shows the difference between deleting a table and simply deleting its data

Recreate the students with a primary key and see its description

CREATE TABLE students (

student_id INT PRIMARY KEY,

name VARCHAR(50),

major VARCHAR(50)

);

DESCRIBE students;

Create a courses table

CREATE TABLE courses (

course_id VARCHAR(10) PRIMARY KEY,

title VARCHAR(100)

);

course_id is the PK even though it is a character/string column rather than an integer

Create an enrollments table with a composite primary key

REATE TABLE enrollments (

student_id INT,

course_id VARCHAR(10),

semester VARCHAR(20),

PRIMARY KEY (student_id, course_id, semester)

);

There is no single-columns PK, the combo of student_id and semester forms the PK

Create a grades table.

What do the first three cols identify?

CREATE TABLE grades (

student_id INT,

course_id VARCHAR(10),

semester VARCHAR(20),

grade VARCHAR(2),

PRIMARY KEY (student_id, course_id, semester)

);

The first three cols uniquely identify a grade record

See al four tables

SHOW TABLES;

Insert several students

INSERT INTO students VALUES

(101, ‘Einstein’, "‘Computer Science’),

(102, ‘Newton’, ‘Mathematics’),

(103, ‘Ramanujam’, ‘Computer Science’);

Insert several courses

INSERT INTO courses VALUES

(‘CS429’, ‘Computer Arch’),

(CS327’, ‘Database Management’),

(‘M408D’, ‘Differential Calculus’);

Insert enrollment records.

What can you tell about the combination?

INSERT INTO enrollments VALUES

(101, 'CS429', 'Fall 2026'),

(101, 'CS327', 'Fall 2026'),

(102, 'M408D', 'Fall 2026'),

(103, 'CS429', 'Fall 2026');

Each combination of student, course, and semester is unique

Insert grade records

INSERT INTO grades VALUES

(101, 'CS429', 'Fall 2026', 'A'),

(101, 'CS327', 'Fall 2026', 'B+'),

(102, 'M408D', 'Fall 2026', 'A-'),

(103, 'CS429', 'Fall 2026', 'B');

Display all records in a table

SELECT * FROM students;

Add one more student

INSERT INTO students VALUES (104, ‘CVRaman’, ‘Physics’);

SELECT * FROM students;

Delete one student

DELETE FROM students WHERE student_id = 104;

SELECT * FROM students

Query selected columns

SELECT name, major FROM students;

Try to insert a duplicate primary key

INSERT INTO students VALUES (101, ‘Feynman’, ‘Physics’);

MySQL rejects the operation with a Duplicate entry error because 101 is already a value in the student_id primary key columns

Examine the primary key and table structure

DESCRIBE enrollments;

all three cols tg form the composite primary key

Demonstrate that a table does not have to have a primary key

CREATE TABLE notes (

note_id INT,

note_text VARCHAR(200)

);

MySQ: will create the table even though no PK has been defined. Relational tables can exist WITHOUT a PK, although this is not desirable for tables representing entities