SQL : Setting Up the Environment

Objectives:

• Install and configure MySQL or PostgreSQL
• Familiarize with SQL client tools (MySQL Workbench, pgAdmin)
• Create a new database and connect to it

1. Installing MySQL/PostgreSQL (10 minutes)

MySQL Installation:

• Download MySQL:
• Visit the MySQL Download Page.
• Choose the MySQL Community Server version.
• Installation Steps:

1. Run the downloaded installer.
2. Follow the installation wizard steps:
   • Select the desired setup type (Developer Default is recommended).
   • Configure the root password.
   • Complete the installation.

• Verify Installation:
• Open MySQL Workbench.
• Connect to the local MySQL server using the root password.
• Execute a simple command to test connection:
  sql SHOW DATABASES;

PostgreSQL Installation:

• Download PostgreSQL:
• Visit the PostgreSQL Download Page.
• Choose the installer for your operating system.
• Installation Steps:

1. Run the downloaded installer.
2. Follow the setup wizard steps:
   • Select the installation directory.
   • Set the password for the default user (postgres).
   • Complete the installation.

• Verify Installation:
• Open pgAdmin.
• Connect to the local PostgreSQL server using the postgres user and the password you set.
• Execute a simple query to test connection:
  sql SELECT version();

2. Using SQL Client Tools (10 minutes)

MySQL Workbench:

• Connecting to Server:

1. Open MySQL Workbench.
2. Click on + to set up a new connection.
3. Enter connection details:
   • Connection Name: Local MySQL
   • Connection Method: Standard (TCP/IP)
   • Hostname: 127.0.0.1
   • Port: 3306
   • Username: root
   • Password: Your root password

• Creating a New Database:

  CREATE DATABASE my_first_db;
  USE my_first_db;

pgAdmin:

• Connecting to Server:

1. Open pgAdmin.
2. Right-click on Servers and choose Create > Server.
3. Configure connection details:
   • General Tab: Server Name (e.g., Local PostgreSQL)
   • Connection Tab: Hostname: 127.0.0.1, Port: 5432, Maintenance database: postgres, Username: postgres, Password: Your password

• Creating a New Database:

  CREATE DATABASE my_first_db;
  \c my_first_db;

3. Creating a Database (10 minutes)

• MySQL Example:

  CREATE DATABASE my_first_db;
  USE my_first_db;
  SHOW TABLES;

• PostgreSQL Example:

  CREATE DATABASE my_first_db;
  \c my_first_db;
  \dt

Exercises

1. Install SQL Software:

• Exercise: Install MySQL or PostgreSQL on your machine. Verify the installation by running a simple SQL command.

1. Connect to the Database Server:

• MySQL: Connect to the MySQL server using MySQL Workbench. Test the connection by running SHOW DATABASES;.
• PostgreSQL: Connect to the PostgreSQL server using pgAdmin. Test the connection by running SELECT version();.

1. Create a New Database:

• MySQL: Create a database named my_first_db. Switch to this database and list the tables. CREATE DATABASE my_first_db; USE my_first_db; SHOW TABLES;
• PostgreSQL: Create a database named my_first_db. Connect to this database and list the tables.
  sql CREATE DATABASE my_first_db; \c my_first_db; \dt

1. Create a Simple Table:

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

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

5. Verify Table Creation:

• Exercise: Verify that the table TestTable exists in my_first_db and insert a sample row.
  sql INSERT INTO TestTable (ID, Name, DateOfBirth) VALUES (1, 'John Doe', '1990-01-01'); SELECT * FROM TestTable;

Homework:

• Reading Assignment:
• Read the first two chapters of “SQL For Dummies” or your chosen SQL textbook.
• Practice Questions:
• Write a brief explanation of how SQL is used in database management.
• List and describe at least three different SQL data types.