This is the time to talk about one of my favorite topic YASSS it's SQL.
SQL (Structured Query Language) is defined as a standard programming language utilized to extract, organize, manage and manipulate data stored in relational databases. It is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database and more.
In this blog we will talk about some basic SQL functions by using the following 'Customer' table which has Customer ID, Name, Age, City and Country of customers.
Now based on this table you have a business scenario - Give me details of all the customer who lives in state New Jersey.
To answer this question we need to understand 'SELECT', 'FROM', 'WHERE' clauses. Let's try to understand these SQL clauses one by one-
Select :- The 'SELECT' command is used to select data from a database. It returns set of records, from one or more tables.
From:- The 'FROM' command is used to specify which table to select or delete data from.
Where:- 'WHERE' clause is used to filter data.
Now the SQL query would be-
SELECT * FROM
Customers
WHERE state='New Jersey';
Above SQL query would return all the customer information from 'Customer' table where state is 'New Jersey' i.e.
We solved first question, It feels so good!!
Second business scenario - What are the cities where customers numbers are at least 2? Provide the result in descending order of city name.
To answer this question we need to use above three clauses(i.e. 'SELECT', 'FROM', 'WHERE') and along with them we need to add few more. To understand first we need to understand Single Row functions and Aggregate functions.
Single Row functions: These functions are used to manipulate data. These functions require one or more input arguments and operate on each row, thereby returning one output value for each row i.e., if we input 10 rows, output will be 10 rows. We can use single row function in 'WHERE' clause. E.g. UPPER, LOWER, TRIM, REPLACE, ROUND, TRUNC, and MOD etc.
e.g. SELECT * FROM customers
WHERE LOWER(city) = 'chicago'
Aggregate functions: These function are where the values of multiple rows are grouped together to form a single summary value i.e. if we input 10 rows, output rows number would be less than 10. Aggregate function can only be used with 'GROUP BY' AND 'HAVING'. E.g. COUNT, SUM, AVG, MAX, MIN etc. We can use same example here which we used for GROUP BY and 'HAVING'.
So let's try to understand what is 'GROUP BY' and 'HAVING' statement.
Group by:- The 'GROUP BY' statement is used to arrange identical data into groups with the help of some functions. In easy way you can say it create different buckets based on some function and put data into that bucket.
Having:- 'HAVING' clause is similar to 'WHERE' clause, only difference is 'HAVING' clause applied on aggregate functions i.e. with 'HAVING', SQL 'SELECT' statement must only return rows where aggregate values meet the specified conditions.
Order by:- 'ORDER BY' is used to sort result set in ascending or descending order. The 'ORDER BY' sorts result set in ascending order by default but we can change it by using 'DESC' keyword.
SELECT city, Count(Customer ID) as NumOfCustomers
FROM Customer
GROUP BY city
HAVING NumOfCustomers >=2
ORDER BY city DESC
Hope this blog will help you understand basics of SQL and get you started with the SQL journey.