WHAT IS A DATABASE ?

A database is an organized collection of data stored electronically.

WHAT IS DBMS?

DBMS stands for Database Management System which is a software used to create,manage and maintain databases.

Types of DBMS:

Image description

WHAT IS SQL?

SQL (Structured Query Language)is a domain-specific language designed for managing and manipulating relational databases. It allows users to:

  • Retrieve data from databases

  • Insert new data

  • Update existing records

  • Delete unwanted data

  • Create and modify database structures

SQL works with database management systems like MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle.

WHY LEARN SQL?

  • Universality: SQL is supported by almost every relational database system.

  • In-demand skill: SQL is among the most sought-after skills in data analytics, software development, and database administration.

  • Data interaction: It empowers you to explore, filter, and summarize vast datasets.

MAIN DATA TYPES IN SQL(POSTGRESQL)

Image description

TABLE CREATION

A table is like a spreadsheet as it stores data in rows and columns.

Basic syntax for table creation

  • CREATE TABLE — command to create a new table.

  • table_name — the name you want to give to your table.

  • column_name — name of each field (column).

  • data_type — type of data the column will store (e.g., text, numbers, dates).

  • constraints (optional) — rules like NOT NULL, PRIMARY KEY, etc.

--creating tables
create table customers(
customer_id SERIAL primary key,
first_name VARCHAR(50) not null,
last_name VARCHAR(50) not null,
email VARCHAR(100) unique not null,
phone_number CHAR (13)
);

Constraint & meaning

  • CREATE TABLE- Create a new table

  • PRIMARY KEY- Uniquely identifies each row

  • FOREIGN KEY -Links tables together

  • NOT NULL -Ensures column cannot be empty

  • VARCHAR(n)- Variable length string

  • DECIMAL(p, s) -Precise numbers with decimals

  • DATE- Date values

  • UNIQUE -all values in the column must be unique

  • DEFAULT -Provides a default value if none is given

COMMON SQL KEY WORDS

  • SELECT-used to retrieve data from a table 
--list book titles and authors
select book_id,title,author
from luxdevteaching.books;
  • WHERE-filters data to show only the rows that meet certain conditons. 
--find orders placed by customer with customer_id 1
select * from luxdevteaching.orders 
where customer_id= 1;
  • ORDER BY -used to sort data .ASC (default) sorts in ascending order while DESC sorts in descending order. 
--list(title and price) books by price from lowest to highest
select title,price
from luxdevteaching.books 
order by price asc;
  • GROUP BY-groups and summarizes data. 
--count how many books each author has
select author,
count(*)
as total_books 
from luxdevteaching.books 
group by author;
  • HAVING-filters after grouping 
--show authors with more than 1 book
select author,
count(*)
as total_books
from luxdevteaching.books 
group by author 
having count(*) > 1;
  • LIMIT-show only a few records 
--show the top 2 most expensive books 
select title,price 
from luxdevteaching.books 
order by price desc 
limit 2 ;