SQL Database Creation and Querying: Hands-On Exercises

Posted on Jan 23, 2025 in Finance, Banking, and Insurance

Starting a New Database

  1. Creating the ‘escola’ database

    CREATE DATABASE escola;

  2. Opening the database for use

    USE escola;

Creating Tables

Creating the ‘aluno’ (student) table

CREATE TABLE aluno (RA INT(4), nome CHAR(30), nascimento DATE, sex CHAR(1), cidade CHAR(30), year INT(1), codcurso INT(3));

Creating the ‘course’ table

CREATE TABLE course (code INT(3), nome CHAR(30), Duracao INT(1));

Inserting Data into Tables

Inserting data into the ‘aluno’ table

Insert yourself and four friends into the ‘aluno’ table:

  1. INSERT INTO aluno VALUES (1234, ‘Jorge Amaral’, ‘1987/02/05’, ‘m’, ‘Americo Brasiliense’, 4, 10);

  2. INSERT INTO aluno VALUES (1235, ‘Vinicius Migliorucci’, ‘1990/04/18’, ‘m’, ‘Bebedouro’, 3, 20);

  3. INSERT INTO aluno VALUES (1236, ‘Wilson Silva’, ‘1970/08/28’, ‘m’, ‘Araraquara’, 4, 20);

  4. INSERT INTO aluno VALUES (1237, ‘Nicole Fontanesi’, ‘1992/06/23’, ‘f’, ‘Bebedouro’, 2, 10);

Exercises: Querying the ‘aluno’ Table

  1. Display the names of students from Bebedouro

    SELECT * FROM aluno WHERE cidade = ‘Bebedouro’;

  2. Display the names of male students

    SELECT nome FROM aluno WHERE sex = ‘m’;

  3. Display the names and genders of students in year 3

    SELECT nome, sex FROM aluno WHERE year = 3;

  4. Display the names and birth dates of students enrolled in course code 10

    SELECT nome, nascimento FROM aluno WHERE codcurso = 10;

Inserting data into the ‘course’ table

First, visualize the structure of the ‘course’ table:

DESCRIBE course;

  1. INSERT INTO course VALUES (10, ‘Web Design’, 3);

  2. INSERT INTO course VALUES (20, ‘Architecture’, 4);

  3. INSERT INTO course VALUES (30, ‘Law’, 5);

Exercises: Querying the ‘course’ Table

  1. Display the names of courses with a duration greater than 3 years

    SELECT * FROM course WHERE Duracao > 3;

  2. Display the name and duration of all courses

    SELECT nome, Duracao FROM course;

Creating and Populating the ‘discipline’ Table

Creating the ‘discipline’ table

CREATE TABLE discipline (codigo INT(3), nome CHAR(40), creditos INT(2), codcurso INT(3));

Inserting data into the ‘discipline’ table

INSERT INTO discipline VALUES (111, ‘Programming II’, 72, 10);
INSERT INTO discipline VALUES (222, ‘Portuguese’, 36, 20);
INSERT INTO discipline VALUES (333, ‘Civil Law’, 72, 30);
INSERT INTO discipline VALUES (444, ‘HCI’, 72, 10);
INSERT INTO discipline VALUES (555, ‘Writing and Hypertext’, 72, 10);

Exercises: Querying the ‘discipline’ Table

  1. Display the names of disciplines associated with course code 10

    SELECT nome FROM discipline WHERE codcurso = 10;

  2. Display the names and course codes of disciplines with fewer than 54 credits

    SELECT nome, codcurso FROM discipline WHERE creditos < 54;

  3. Display all disciplines

    SELECT * FROM discipline;