Introduction

SQL

SQL stands for Structured Query Language. It's the standard language for relational database management systems. SQL allows you to:

SQL Helps To:

Manage data in a relational database.

1. Access databases: SQL helps in securely connecting to databases.
2. Manipulate Databases: SQL helps in insertion, updating, and deletion of data, as well as the creation and alteration of database structures.
3. Query Databases: SQL is used to retrieve and analyze data using queries, including filtering, sorting, and joining tables.

Types of SQL commands

• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

Relational Database

Databases are organized collections of data that can be easily accessed, managed, and updated. They are used in various applications, from banking systems to websites.

The most common type of database is a relational database, which organizes data into tables.

1. Tables (Relations):: table stores data in rows and column, each table has unique name in database.
2. Primary Key: A primary key is a column (or a set of columns) that uniquely identifies each row in a table.
3. Foreign Key: A foreign key is a column in one table that links to the primary key in another table. This relationship creates a connection between the two tables.
4. Relationships: Relationships can be one-to-one, one-to-many or many-to-many.
   • One-to-One: One row in a table is linked to exactly one row in another table.
   • One-to-Many: One row in a table is linked to multiple rows in another table. This is the most common relationship.
   • Many-to-Many: Multiple rows in one table are linked to multiple rows in another table, typically handled by creating a junction table.

A SQL table is basic element of relational database. We can create relationaships between multiple database tables.

Example for Relational Database: Consider a simple Customer Management System.

To create a database for a Customer Management System (CMS), we'll design a relational database schema that includes tables to store information about customers, orders, products, and interactions. The database should support typical CMS functionalities such as storing customer details, tracking orders, managing products, and recording customer interactions.

Database Schema Design

• Customers Table: Stores customer details.
• Orders Table: Stores information about orders placed by customers.
• Products Table: Stores information about products available for sale.
• Order_Items Table: Stores details of each item within an order.
• Interactions Table: Stores records of interactions with customers, such as emails, phone calls, etc.
• Retailers Table: Stores details of each retailers.

Relational Structure

The tables in the CMS are interconnected to maintain data integrity and support complex queries. Here's how the relationships are structured:

• Customers ↔ Orders: A one-to-many relationship; each customer can have multiple orders.
• Orders ↔ Order_Items: A one-to-many relationship; each order can consist of multiple items.
• Order_Items ↔ Products: A many-to-one relationship; each order item is associated with a specific product.
• Customers ↔ Interactions: A one-to-many relationship; each customer can have multiple interactions.

Keys

• Customers Table

  • customer_id (PRIMARY KEY)
  • first_name
  • last_name
  • customer_name
  • email
  • phone
  • address
  • city
  • state
  • zipCode
  • country
  • date_created

• Orders Table

  • order_id (PRIMARY KEY)
  • customer_id (FOREIGN KEY)
  • order_date
  • status
  • total_amount

Conclusion

SQL stands for Structured Query Language. It's the standard language for relational database management systems. SQL allows you to:

• Create databases and tables
• Insert, update, and delete data
• Retrieve data using queries
• Manage database access and security

Asked in Interview

• What is SQL?
• Explain table relationships in SQL.
• What are the different types of SQL commands?
• What is a Relational Database?
• What is the difference between a relational and non-relational database?