Structured Query Language, or SQL, is a potent language used for database management and manipulation. It was developed in the 1970s and is now one of the most important tools for handling data in modern times. In relational databases, SQL enables us to create, retrieve, update, and delete (CRUD) data. There's a good possibility you've dealt with SQL if you've ever worked with data!
What is a database?
All that a database is, is a collection of organized or meaningful data. Imagine it as a much larger version of an orderly spreadsheet with rows and columns. Databases can hold vast amounts of text, similar to a library of books, as well as information such as customer records and product catalogs.
Common SQL Commands
SELECT – Retrieve data from a database.
INSERT – Add new data to a database.
UPDATE – Modify existing data.
DELETE – Remove data.
CREATE – Create a new database or table.
DROP – Delete a table or database.
SQL Syntax: Basic Structure
SQL commands are written as statements. A basic SQL query looks something like this:
SELECT column_name
FROM table_name;
It’s as simple as that! Let's say you want to view all the customer names from a table called customers. You’d write:
SELECT customer_name
FROM customers;
How to Set Up SQL:
To get started with SQL, you’ll need a SQL environment. This could be a database management system (DBMS) like MySQL, PostgreSQL, or SQLite. Once you’ve installed one of these, you can start writing SQL commands to interact with your database.There are also cloud-based SQL services if you prefer to work in the cloud.
Creating a Database
Creating a database is one of the first things you’ll do. It’s like setting up a new workbook for a project. Here’s how you can create a new database in SQL:
CREATE DATABASE Technology;
This command creates a database called Technology. Now you can create tables inside this database to store your information!
If you want to check if the database is created, you use the command:
show databases;
Tables in SQL
Tables are where all your data lives. Each table is organized into rows and columns, where:
Rows = individual records (e.g., one customer’s details).
Columns = categories of data (e.g., customer names, email addresses, etc.).
Let’s create a basic table for storing customer information:
CREATE TABLE customers (
customer_id INT,
customer_name VARCHAR(100),
email VARCHAR(100),
age INT
);
This command creates a customers table with four columns: customer_id, customer_name, email, and age. Each customer will have a unique customer_id (because of PRIMARY KEY), and the VARCHAR(100) defines the length of text that can be stored in the column.
Inserting Data into a Table
Once you have a table, it’s time to fill it with data! To add a new customer, we use the INSERT statement.
INSERT INTO customers (customer_id, customer_name, email, age)
VALUES (1, 'Faith Jeptoo', 'faykibowen@gmail.com', 20);
This command adds a new row to the customers table. Each value corresponds to a column in the table.
Retrieving Data from a Table
To see the data we’ve inserted, we use the SELECT statement:
SELECT * FROM customers;
The * means "all columns," so this query retrieves everything from the customers table. You should see something like this:
+-------------+---------------+-----------------------+---------+
| customer_id | customer_name | email | age |
+-------------+---------------+-----------------------+---------+
| 1 | Faith Jeptoo | faykibowen@gmai.com | 20 |
+-------------+---------------+-----------------------+---------+
Data Manipulation
use accenture_my;
show tables;
this will shows all the tables in the database
LIMIT
SELECT * from content;
limit 3;
The limit function will only shows records from 3 rows in the content table
DISTINCT
Helps avoid duplicates by choosing unique values/records.
SELECT DISTINCT(column_name)
from content;
SELECT * from reactions;
column_name is the category.
If we hade two hates from the column, the the utcome will choose one avoiding duplicates.
Filtering Data
If you only want to retrieve specific data, you can add a condition using the WHERE clause. For example, to get all customers who are 25 years old, you can write:
SELECT *
FROM Africa;
WHERE country = 'Kenya';
This will return just the names of people from Kenya.
Updating Data
If John Doe’s email changes, we can update it with the UPDATE command:
UPDATE customers
SET email = 'john.new@example.com'
WHERE customer_id = 1;
This command updates the email for the customer with the ID of 1.
Deleting Data
To delete a customer from the database, we can use the DELETE command:
DELETE FROM customers
WHERE customer_id = 1;
This will remove the customer with ID 1 from the table.
SQL Data Types
SQL tables support various data types to store different kinds of information. Here are a few common ones:
INT – Whole numbers.
VARCHAR – Variable-length text.
DATE – Stores dates.
FLOAT – Decimal numbers.
Joins: Combining Data from Multiple Tables
Sometimes, data is spread across multiple tables. You can use a SQL JOIN to combine related data. For example, if you have an orders table and a customers table, you can link them with a join to see which customer placed which order.
SELECT customers.customer_name, orders.order_id
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
Wrapping Up
SQL is a simple yet incredibly powerful language for managing and working with databases. From retrieving data to manipulating tables, SQL is the go-to language for anyone looking to manage data efficiently. Whether you’re working with small datasets or managing big databases, SQL is a must-learn!
CTE
Common Table Expression-I want the results of a certain query or statement be stored with a given. Somewhat like variables in C.
WITH kibowen AS (
SELECT *
FROM reactions
WHERE react = 'Disgust');
Final Thoughts
Learning SQL might seem intimidating at first, but with practice, you’ll get the hang of it. Start small—try creating your first database, add some tables, insert data, and experiment with basic queries. Before you know it, you'll be a SQL pro! Happy querying!
Top comments (0)