Skip to content

Latest commit

 

History

History
124 lines (88 loc) · 3.11 KB

SQL2.md

File metadata and controls

124 lines (88 loc) · 3.11 KB
Raw

Topic: SQL Table Operations

Introduction:

  • SQL (Structured Query Language) is a programming language used for managing relational databases.
  • It provides various commands to perform operations on the database tables such as create, update, insert, delete, and select.

I. Basic Commands:

A. Show Database - to show all the databases available on your system.

SHOW DATABASES;

B. Select Database - to select a particular database to run queries on.

USE [database_name];

C. Show Tables - to list down all the tables of a particular selected database.

SHOW TABLES;

D. Create Database - to create a new database.

CREATE DATABASE [database_name];

E. Create Table - to create a new table.

CREATE TABLE [table_name] (
[column_name_1] [datatype_1],
[column_name_2] [datatype_2],
...
);

F. Describe Table - to show the structure and description of the table.

DESC [table_name];

G. Insert Values - to insert values into a table.

INSERT INTO [table_name] ([column_name_1], [column_name_2], ...)
VALUES ([value_1], [value_2], ...);

H. Select Data

--- to get specific column data from a table.
SELECT [column_name] FROM [table_name]; 

-- to get all the columns.
SELECT * FROM [table_name]; 

-- to get filtered data based on the given conditions.
SELECT [column_name] FROM [table_name] WHERE [conditions];

II. Advanced Commands:

A. Delete Rows - to delete rows from a table based on certain conditions.

DELETE FROM [table_name] WHERE [conditions] ORDER BY [column_name] LIMIT [number];

B. Update Rows - to update rows in a table based on certain conditions.

UPDATE [table_name] SET [column_name_1] = [value_1] WHERE [conditions];

C. Alter Table

-- to modify an existing column.
ALTER TABLE [table_name] CHANGE [old_column_name] [new_column_name] [datatype/constraint]; 

-- to add a new column to the table.
ALTER TABLE [table_name] ADD [column_name] [datatype/constraint];

D. Aggregate Functions - to perform calculations on columns.

SELECT function_name FROM [table_name];

Functions include SUM, AVG, COUNT, and DISTINCT.

E. Group By - to group data based on a particular column.

SELECT [column_name], COUNT(*) FROM [table_name] GROUP BY [column_name];

F. Order By - to sort data in ascending or descending order.

SELECT [column_name] FROM [table_name] ORDER BY [column_name] [ASC/DESC];

G. Limit - to limit the number of rows returned by the query.

SELECT [column_name] FROM [table_name] LIMIT [number];

H. Concatenate Strings - to combine two or more strings into a single column.

SELECT CONCAT([column_name_1], ' ', [column_name_2]) AS [new_column_name] FROM [table_name];

Conclusion:

SQL provides various commands to perform operations on tables in a database. The basic commands include show database, select database, show tables, create database, create table, describe table, insert values, and select data. The advanced commands include delete rows, update rows, alter table, aggregate functions, group by, order by, limit, and concatenate strings.