some stuff sql
to select distinct/unique fields available in the table
select distinct column_name from table_name;
Where clause?
the where clause is used to filter records.
it is used to extract only those records that fulfils a specified
condition
select column_name from table_name
where conditions;
operators in sql
[Link] operators:
Example: Addition (+), Subtraction (-), Multiplication (*), Division (/),
Modulus (%)
[Link] operators:
• Example: Equal (F), Not Equal (!=), Greater Than (>), Greater Than
Equals +
3. Logical operators: perform the Boolean operations
• Example: ALL, IN, BETWEEN, LIKE, AND, OR, NOT, ANY
4. Bitwise operators: perform the bit operations on the In
• Example: Bitwise AND (&), Bitwise OR(l)
LIMIT Clause:
The LIMIT clause is used to set an upper limit on the number of tuples
returned by
SQL.
Example: below code will return 5 rows of data
SELECT column_name FROM table_name
LIMIT 5;
ORDER BY Clause:
The ORDER BY is used to sort the result-set in ascending (ASC) or
des order (DESC).
Example: below code will sort the output data by column name in as
SELECT column_name FROM table_name
ORDER BY column_name e ASC;
+)Functions In SQL
Functions in SQL are the database objects that contains a set of SQL
statements to perform a specific task. A function accepts input
parameters, perform actions, and then return the result.
Types of Function:
1. System Defined Function: these are built-in functions
• Example: rand(), round), upper), lower), count(), sum(),max(), etc
2. User-Defined Function: Once you define a function can call it in the
same way as the built-in functions
most used string function
Most Used String Functions
String functions are used to perform an operation on input string and
return an output string
● UPPER() converts the value of a field to uppercase
● LOWER() converts the value of a field to lowercase
● LENGTH() returns the length of the value in a text field
● SUBSTRING() extracts a substring from a string
● NOW () returns the current system date and time
● FORMAT() used to set the format of a field
● CONCAT() adds two or more strings together
● REPLACE() Replaces all occurrences of a substring within a
string, with a new subst
● TRIM() removes leading and trailing spaces (or other
specified characters) from a string
mysql> select upper (name) from student;
+--------------+
| upper (name) |
+--------------+
| MANAN |
| MALKEET |
| SHYAM |
| ROHIT |
| PRAKHAR |
+--------------+
5 rows in set (0.04 sec)
mysql> select length(name) from student
-> order by length(name) asc;
+--------------+
| length(name) |
+--------------+
| 5|
| 5|
| 5|
| 7|
| 7|
+--------------+
5 rows in set (0.00 sec)
mysql> Select concat (name ,lname) AS full_name
-> from student
-> order by name asc;
+----------------+
| full_name |
+----------------+
| MAlkeetSingh |
| MANANPatel |
| Prakharsinghal |
| Rohitsoni |
| ShyamPratap |
+----------------+
5 rows in set (0.01 sec)
Most Used Aggregate Functions
Aggregate function performs a calculation on multiple values and
returns a single value.
And Aggregate functiona are often used with GROUP BY & SELECT
statement
● COUNT() returns number of values
● SUM() returns sum of all values
● AVG() returns average value
● MAX) returns maximum value
● MIN() returns minimum value
● ROUND) Rounds a number to a specified number of decimal
places
mysql> select avg (age) from student;
+-----------+
| avg (age) |
+-----------+
| 19.6000 |
+-----------+
1 row in set (0.00 sec)
mysql> select round(avg(age),1) from student;
+-------------------+
| round(avg(age),1) |
+-------------------+
| 19.6 |
+-------------------+
1 row in set (0.01 sec)
GROUP BY Statement
The GROUP BY statement group rows that have the same values into
summary rows.
It is often used with aggregate functions (COUNT, MAX), MIN(, SUM(,
AVG()) to group the result-set by one or more columns
● Syntax
SELECT column_name(s)
FROM table_name
GROUP BY column_name(s);
● Example
SELECT mode, SUM(amount) AS total
●
FROM payment
GROUP BY mode
ORDER BY total asc;
HAVING Clause(just like where condtion)
The HAVING clause is used to apply a filter on the result of GROUP
BY based on the specified condition.
The WHERE clause places conditions on the selected columns,
whereas the HAVING clause places conditions on groups created by
the GROUP BY clause
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition(s)
GROUP BY column_name(s)
HAVING condition(s)
• Example
SELECT mode, COUNT(amount) AS total
FROM payment
GROUP BY mode
HAVING COUNT(amount) >= 3
ORDER BY total DESC
SQL JOIN
● JOIN means to combine something.
● A JOIN clause is used to combine data from two or more
tables, based on a related column between them
● Let's understand the joins through an example:
inner join->returns records that have matching values in both tables
INNER JOIN
● Syntax
SELECT column_name(s)
FROM TableA
INNER JOIN TableB
ON TableA.col_name = TableB.col_name
●
● Example
SELECT *
FROM customer AS c
INNER JOIN payment AS p
ON c.customer_id = p.customer_id
l
left join ->Returns all records from the left table, and the matched
records from the right table
right join->• Returns all records from the right table, and the matched
records from the left table
FULL JOIN
• Returns all records when there is a match in either left or right table
SELF JOIN
● A self join is a regular join in which a table is joined to itself
● SELF Joins are powerful for comparing values in a column of
rows with the same table
● Syntax
SELECT column_name(s)
FROM Table AS T1
JOIN Table AS T2
● ON T1.col_name = T2.col_name
UNION
The SQL UNION clause/operator is used to
combine/concatenate the results
of two or more SELECT statements without returning any duplicate
rows and keeps unique records
To use this UNION clause, each SELECT statement must have
● The same number of columns selected and expressions
● The same data type and
● Have them in the same order
● Syntax
SELECT column_name(s) FROM TableA
UNION
SELECT column_name(s) FROM TableB
● Example
SELECT cust_name, cust_amount from custA
UNION
SELECT cust_name, cust_amount from custB
UNION ALL
In UNION ALL everything is same as UNION, it combines/concatenate
two or more table but keeps all records, including duplicates
● Syntax
SELECT column_name(s) FROM TableA
UNION ALL
SELECT column_names) FROM TableB
● Example
SELECT cust_name, cust_amount from custA
UNION ALL
SELECT cust_name, cust_amount from custB
VIEW: Views are small part of sql. In SQL views are kind of virtual
tables. A view also has rows and columns as they are in a real table in
the database. We can create a view by selecting fields from one or
more tables present in the database.
•
• Type here to search
syntax : CREATE VIEW view name AS SELECT column1, column2,...
FROM table name WHERE condition;
Example:
CREATE VIEW stu_View AS SELECT NAME, ADDRESS FROM college
WHERE S_ID
< 5;
to see the result:
SELECT * FROM stu_view;
Output :
name address
ani
goa
nia
chennai