My Learning_SQL2
Objectives
-Retrieve data with SELECT and filter with where clause with LIKE, IN, and wildcards
-Filtering and sorting results
-Transforming results with functions
-Grouping SQL results
-Merging data with JOINS
Retrieve data with SELECT and filter with where clause with LIKE, IN, and wildcards:
The SELECT statement is used to select data from a database.
<> or != Not equal operator , single quotes should be used to enclose text
Its a good practice to use single quotes.select first_name from profile where first_name <> ‘Devender’;
select first_name from profile where first_name = ‘Devender’ OR first_name=’Dev’ OR first_name=’dp’;
If we want to save a bit of typing we can use the IN/ NOT IN(to exclude) statements when adding WHERE clauses to the same field. So the IN statement saves us more time, when we have more values.
select first_name from profile where first_name IN( ‘Devender’, ‘Dev’, ‘dp’);
select first_name from profile where first_name NOT IN( ‘Devender’, ‘Dev’, ‘dp’);
Wild Cards:
We can use wild cards to match part of a text. If we want to select any profile with a name starting with the letters ‘Dev’ then we would use the LIKE keyword and the wild card we are going to use is the percent sign. The percent(%) sign will match any value which including no value or multiple values.
select first_name from profile where first_name LIKE ‘Dev%’;
An underscore(_) is another wild card character for matching a single letter. So we can say De_ender wth an underscore in the middle of it. The underscore will match any kind of character but there must be one letter and It won’t match a blank.
select first_name from profile where first_name LIKE ‘D_v%’;
Combining ANDs with ORs, if you can see a number of the fields here like address and district and so on. Actually you can run a query on a table without clicking on it. We could have run it from the previous screen but I wanted to give you the chance to see the field names down here. So what we want is
SELECT * FROM ‘profile’ WHERE first_name = ‘Devender’ AND (last_name LIKE %pal% OR last_name LIKE %dp%);
Notice that the case we used in WHERE clause was irrelevant so these sorts of enquiries are case insensitive and also notice that we used parentheses around the OR statement, this is to help the database software read the query as we intend it. If we wont use brackets the database will read and apply the filters in the order they appear.
Sort the results using ORDER BY:
SELECT first_name, last_name FROM ‘profile’ WHERE first_name = ‘Devender’ ORDER BY last_name DESC;
Transform SQL results with string functions
SQL provides lots of functions to help we manipulate our data as we retrieve it. Some functions will work only on text fields and some others will work only on numeric fields or date fields.
For example Length, length counts the numbers of characters in a string or indeed a number.
SELECT first_name, LENGTH(first_name) FROM ‘profile’;
Most of the SQL functions have same form, the function name followed by parentheses and then the field name inside it and this function creates a whole new column( derived or synthetic).
CONCAT is an another common string function which is short form of concatenate which is a common function in any language and it just adds strings together. If we’re using Oracle or PostgreSQL you could write for example, first_name and then use the double pipe to indicate concatenation. And if you’re using Microsoft, instead of a double pipe it’s a plus sign like so
If we concatenate the word “Dev” and the word “ender”, then we will get ‘Devender’.
SELECT CONCAT(first_name, ‘ ‘, last_name) FROM profile; for example here we get ‘Devender Palsa’
If w’re using Oracle or PostgreSQL then we have to use the double pipe(||) to indicate concatenation and if w’re using , Microsoft instead of a double pipe we have to use plus(+) sign. But CONCAT works across all of the functions, we can also combine string functions by placing one of them inside the other.
SELECT LENGTH(CONCAT(first_name, ‘ ‘, last_name)) FROM profile ORDER BY CONCAT(first_name, ‘ ‘, last_name) DESC;
SELECT LOWER(first_name) FROM profile;
SELECT RIGHT(first_name, 3) FROM profile; here we get Dev from Devender
SELECT LEFT(first_name, 5) FROM profile; here we get ender from Devender
We need to use LENGTH function to work irrespective of length:
SELECT CONCAT(LEFT(first_name, 3), LEFT(first_name, LENGTH(first_name)-1)) FROM profile;
Advanced string functions: SUBSTRING, TRIM and LOCATE
SUBSTRING(to extract part of the string) function takes two to three parameters (the variable name and then two numerical values, one takes the starting character which is not zero and then the number of the characters we want.
TRIM function to remove leading and trailing spaces, in Microsoft instead of TRIM we have LTRIM and RTRIM functions.
LOCATE function gives the location or the position of one string inside another, MySQL and Oracle, we can use LOCATE but in PostgrSQL equvalent function is POSITION and in Microsoft it is a CHARINDEX.
SELECT SUBSTRING(first_name, 1, 3) FROM profile;
SELECT TRIM(first_name) FROM profile;
SELECT first_name, LOCATE(‘de’, first_name) FROM profile;
Change SQL result headings with an alias using AS name, alias is case sensitive:
SELECT CONCAT(first_name, ‘ ‘, last_name) AS name FROM profile;
Data types in SQL databases
There are more than 30 datatypes but they all are grouped into number, text and date, nearly 20 are numeric, ten for text and five for date and time in my SQL.
If we don’t have access to a database even though we can get the information we need, in my SQL and oracle, we can use a DESCRIBE keyword but this keyword doesn’t work for Microsoft or PostgreSQL in which information_schema and performance_schema, both of these will be created automatically in SQL, Microsoft and Postgre but not in Oracle. The information_schema database automatically contains descriptions of your data and Performance_schema contains details about how fast our database is performing its actions.
DESCRIBE ‘profile’;
Columns table contains descriptions of all the fields that w’re using as well as some fields that w’re not going to be using at all.
SELECT * FROM `COLUMNS` WHERE table_name = ‘profile’;
Date functions:
YEAR function works in MySQL and in Microsoft but in Oracle or PostgreSQL, we would use extract or DATE_PART. In a similar way with YEAR , we can use MONTH, WEEK, DAY, HOUR, MINUTE and SECOND in SQL.
SELECT * FROM `drug_info` WHERE YEAR(first_dose_date)=2020;
YEAR function works in MySQL and in Microsoft but in Oracle or PostgreSQL, we would use extract or DATE_PART. In MySQL, along with YEAR, you can use MONTH, WEEK, DAY, HOUR, MINUTE and SECOND in a similar way.
In MySQL and PostgreSQL, we can just use DATE, in Microsoft we need to use the CONVERT function and in Oracle we will use TO_DATE with TO_CHAR.
We use DATE_FORMAT in MySQL, in Oracle and PostgreSQL instead of DATE_FORMAT we use TO_CHAR. In a format string that consists of pre-defined specifiers, each specifier is preceded by a percentage (%) notation in MySQL.
DATE_FORMAT(date,format);
SELECT DATE_FORMAT(first_dose_date, ‘%m-%d-%Y’) from `drug_info`;
SELECT DATE_FORMAT(first_dose_date, ‘%D % M % Y’) from `drug_info`;
Aggregate results with GROUP BY
SELECT DATE_FORMAT(first_dose_date, ‘%D % M % Y’) from `drug_info`;
SELECT state, count(*) AS total from `country` WHERE country_code < 7 GROUP BY state ORDER BY total DESC;
Filter grouped results with HAVING with SUM, COUNT, AVG:
HAVING clause is like a WHERE clause for aggregated data or grouped data. If we use a HAVING clause then have to use ORDER BY at the end, always SQL queries follow this pattern.
SELECT state from `country` GROUP BY state HAVING count(state) >7 ORDER BY count(state) DESC;
Reporting unique values with SELECT DISTINCT:
SELECT DISTINCT state FROM `country`
We can also use SELECT DISTINCT with functions like CONCAT, YEAR and COUNT DISTINCT will works across all the versions of SQL.
SELECT DISTINCT CONCAT (first_name,last_name) FROM `profile`;
SELECT DISTINCT YEAR (first_dose_date) FROM `drug_info`;
SELECT COUNT(DISTINCT YEAR (first_dose_date)) FROM `drug_info`;
Merge rows with GROUP BY with GROUP_CONCAT:
GROUP_CONCAT is a string function which can run on grouped data(GROUP_CONCAT in MySQL, LISTAGG in Oracle and both array_agg and string_agg in PostGreSQL).
SELECT DISTINCT state, GROUP_CONCAT(contact ORDER BY contact SEPERATOR ‘;’) FROM `country` GROUP BY state;
Limiting the report size using LIMIT (MySQL and PostGreSQL)/ROWNUM(Oracle)/SELECT TOP(Microsoft).
Indexes and Keys
A Primary Key is a special kind of field and is an integer and also it’s the first field in the table which uniquely identifies all the rows in the table, which should be unique and not null.
A foreign key(FK) is a column or group of columns in a database table that provides a link between data in two tables and it acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
An Index is a field that’s been flagged to the database as being a useful search column. Creating an index creates data that describes the table to help the database find rows faster but the negative side is that indexes themselves take up space and having too many indexes can make your database run slowly. We would only put an index on a column.
Merge data from multiple tables with JOIN and Combine rows with UNION and UNION ALL:
We can merge tables together using Join, joins allow us to merge columns from different tables, it is like a horizontal merge.
We can merge rows into the same columns using UNION and UNION ALL and It’s like a vertical merge.
By default UNION performs a select distinct(it only gives unique values) but unlike UNION, UNION ALL gives all of the rows from the first table, followed by all of the rows from the second table.
SELECT first_name, state FROM people
JOIN states
ON people.state=states.state_abbrev;
SELECT ‘table1_drug’ as tb, date(first_dose_date) FROM `drug_info`
UNION
SELECT ‘table2_profil’ as tb, first_name FROM `profile`
SELECT ‘table1_drug’ as tb, date(first_dose_date) FROM `drug_info`
UNION ALL
SELECT ‘table2_profil’ as tb, first_name FROM `profile`
“Every derived table must have its own alias” for example below subquery gives us a derived table:
SELECT a.first_name FROM (SELECT first_name from profile) as a
View is a stored query/a virtual table based on the result-set of an SQL statement. A view contains rows and columns like a real table but this is not a table and the data is not stored in the view.
Functions:
A function is just like functions in Excel, which doesn’t edit or delete the data and even doesn’t perform an action, it just takes an input and gives an output.
SHOW CREATE FUNCTION function_name;
Stored Procedures:
A stored procedure is a a group of SQL statements/prepared SQL code that has been created and stored in the database, so the code can be re-used over and over again. The stored procedure may have an in parameters and then out parameter, it starts with begin and ends with end, between contain our query.
We can use functions within stored procedures but we can’t do the opposite.
we can call the procedure using the call keyword.
CALL procedure_name(input_parameters, output_parameter);