Introduction to SQL and Databases

Objectives:

• Understand the basics of databases and SQL
• Get familiar with relational database concepts
• Learn basic SQL syntax

1. Introduction to Databases

• Definition of a Database:
• A database is an organized collection of structured information or data, typically stored electronically.
• Examples: Customer records, product catalogues, etc.
• Types of Databases:
• Relational Databases: Data is organized into tables (relations) with rows and columns. Examples: MySQL, PostgreSQL, Oracle.
• NoSQL Databases: Data is stored in formats like key-value pairs, documents, or graphs. Examples: MongoDB, Cassandra.
• Relational Database Concepts:
• Table: A collection of rows and columns.
• Row: A single data record.
• Column: A data field in a table.
• Primary Key: A unique identifier for a table row.
• Foreign Key: A field that links to the primary key of another table.

2. Introduction to SQL

• What is SQL?
• SQL (Structured Query Language) is used to interact with relational databases.
• Common SQL commands include SELECT, INSERT, UPDATE, DELETE, CREATE, and ALTER.
• Basic SQL Syntax:
• SQL statements are written in plain text.
• SQL keywords are case-insensitive.
• Basic syntax structure: COMMAND [options] [conditions];
• Basic SQL Commands:
• SELECT: Retrieve data from a table.
• INSERT INTO: Add new rows to a table.
• UPDATE: Modify existing data.
• DELETE: Remove rows from a table.
• CREATE TABLE: Create a new table.
• ALTER TABLE: Modify an existing table.

3. Environment Setup

• Installing MySQL/PostgreSQL:
• Provide instructions for downloading and installing the database management system.
• MySQL: Download MySQL
• PostgreSQL: Download PostgreSQL
• Using SQL Tools:
• MySQL Workbench: A graphical tool for MySQL.
• pgAdmin: A graphical tool for PostgreSQL.
• Creating a New Database:
• Open the SQL client.
• Create a new database using the following command:
  sql CREATE DATABASE my_first_db;
• Use the newly created database:
  sql USE my_first_db; -- MySQL \c my_first_db; -- PostgreSQL

Exercises

1. Identify Database Types:

• Exercise: List at least two examples of relational databases and two examples of NoSQL databases.

1. Basic SQL Command Syntax:

• Exercise: Write the basic syntax for the following SQL commands:
  • SELECT
  • INSERT INTO
  • UPDATE
  • DELETE
  • CREATE TABLE
  • ALTER TABLE

1. Install SQL Software:

• Exercise: Install MySQL or PostgreSQL on your local machine. Ensure that you can open the SQL client (MySQL Workbench or pgAdmin).

1. Create and Use a Database:

• Exercise:
  1. Create a new database named test_db.
  2. Select the database for use.

1. Create Your First Table:

• Exercise: Create a simple table named TestTable with the following columns:
  • ID (INTEGER, Primary Key)
  • Name (VARCHAR(50))
  • DateOfBirth (DATE)

   CREATE TABLE TestTable (
       ID INT PRIMARY KEY,
       Name VARCHAR(50),
       DateOfBirth DATE
   );

6. Review and Practice:

• Exercise: Review the SQL commands and practice using them by running sample queries on the TestTable.

Homework:

• Reading Assignment:
• Read introductory chapters on databases and SQL from the recommended textbook or online resources.
• Practice Questions:
• Briefly explain what a primary key is and why it’s important.
• Create a new table with at least three different data types.

This detailed plan for Day 1 provides a comprehensive introduction to databases and SQL, including both theoretical concepts and practical exercises.