{"id":1890,"date":"2019-08-19T02:39:43","date_gmt":"2019-08-19T02:39:43","guid":{"rendered":"http:\/\/kusuaks7\/?p=1495"},"modified":"2024-05-01T10:16:28","modified_gmt":"2024-05-01T10:16:28","slug":"sql-case-study-helping-a-startup-ceo-manage-his-data","status":"publish","type":"post","link":"https:\/\/www.experfy.com\/blog\/bigdata-cloud\/sql-case-study-helping-a-startup-ceo-manage-his-data\/","title":{"rendered":"SQL Case Study: Helping a Startup CEO Manage His Data"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tYou may have heard that if you know English, you can mostly write queries with SQL. This is almost right, because SQL is very intuitive, and it is really like plain English.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIn this tutorial, you will learn how to create a table, insert values into it, use and understand some data types, use SELECT statements, UPDATE records, use some aggregate functions like COUNT(), MIN(), SUM(), use WHERE clause, remove a character from a string, cast a string to numeric values, write subquery, retrieve the top and bottom candidates, add a column, do a for loop, and make a histogram.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThough, if you love videos more than articles while learning, I have published this article as a practical course at\u00a0\u201cIntuitive SQL Database Case Study<\/strong><\/a>\u201d. This course includes all the snippets of codes written here and more in an interactive way on a SQL shell.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tAll the previous tasks can be done by showing a case study to help you find some guidance to play with data in a practical way. The best thing to do while reading this tutorial is to write the codes I write and please do yourself a favor and make your hands dirty especially if you are a beginner because this is what makes you brilliant in this language (actually true for almost anything you learn). If you know some SQL, I hope you find this article useful as well and see some other things might add to your knowledge.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tI\u2019m working with PostgreSQL which is a general-purpose database management system, but you can still do all the codes for MySQL, Oracle, DB2, etc. If you just happen to find that a piece of code does not work, please ask google about the syntax of the database management system you use.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s say, we would like to have this table:\n\n\n\n\n\n\n\n\n\n\n
Id<\/span><\/span><\/td>\nname<\/span><\/span><\/td>\nage<\/span><\/span><\/td>\ncity<\/span><\/span><\/td>\nsalary<\/span><\/span><\/td>\n<\/tr>\n
1<\/td>\nWael<\/td>\n23<\/td>\nCairo<\/td>\n800$<\/td>\n<\/tr>\n
2<\/td>\nRagab<\/td>\n24<\/td>\nGiza<\/td>\n900$<\/td>\n<\/tr>\n
3<\/td>\nAli<\/td>\n24<\/td>\nGiza<\/td>\n1500$<\/td>\n<\/tr>\n
4<\/td>\nOthman<\/td>\n25<\/td>\nGiza<\/td>\n2000$<\/td>\n<\/tr>\n
5<\/td>\nWaleed<\/td>\n26<\/td>\nCairo<\/td>\n2100$<\/td>\n<\/tr>\n
6<\/td>\nAbdo<\/td>\n23<\/td>\nAlex<\/td>\n900$<\/td>\n<\/tr>\n
7<\/td>\nMoetaz<\/td>\n22<\/td>\nCairo<\/td>\n800$<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nCreating a table:<\/b>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe can create such a table using the following query:\n
CREATE TABLE startup(\nid int NOT NULL,\nname varchar(10) NOT NULL,\nage int CHECK(age > 0),\ncity varchar(10),\nsalary varchar(5),\nPRIMARY KEY(id));<\/span><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s see in more detail what happened for what we wrote. We created a table using create(). The table is called startup and it includes the id city with the data type integer with no possible NULL values; we\u2019ll see why later. The second column is \u2018name\u2019 whose data type is a varchar(10) which is a variable-length character of 10 characters i.e. \u2018Ali\u2019 will take 3 character-size in memory while \u2018Othman\u2019 will take 6 character-size in memory. This column can also be stored as a char which is a fixed-length character of 10 but in this case \u2018Ali\u2019 will take 10 character-size in memory while \u2018Othman\u2019 will take also 10 character-size in memory. The \u2018name\u2019 column can also be stored as a text which is not very wise for storage because text can take up to 2GB of text data in memory. When writing queries, you should put storage into consideration because your table can contain large amounts of data and retrieving them may not be as fast as you desire.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tBack to the query, the \u2018name\u2019 column cannot be NULL (i.e. should contain a name not nothing). The \u2018age\u2019 column contains integer values and only the positive numbers are allowed. The \u2018city\u2019 and \u2018salary\u2019 contain variable-length character of 10 and 5 characters respectively. Finally, the \u2018id\u2019 column contain unique values (a primary key). This means it cannot contain duplicate values (integers in our case). This syntax could be done when defining id as int, so it can be done as such \u2018id int primary key not null\u2019.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tNotice that SQL is case insensitive, so any key words from the above query can be lower case, but it\u2019s a good practice to make them in upper case.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Retrieving the data:<\/b><\/h3><\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tNow, we can retrieve what we have so far through:\n
SELECT *\nFROM startup;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe SELECT clause is the most used key word in SQL. This query simply can retrieve all the data from the startup table. As we can use from the result, it includes all the columns we created with 0 rows in it.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Inserting values into a table:<\/b><\/h3><\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s fill the first row in this table:\n
INSERT INTO startup\nVALUES(1, ‘Wael’, 23, ‘Cairo’, ‘800$’);<\/span><\/div>\n \n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tNow the first row is done:\n\n\n\n\n
Id<\/span><\/span><\/td>\nname<\/span><\/span><\/td>\nage<\/span><\/span><\/td>\ncity<\/span><\/span><\/td>\nsalary<\/span><\/span><\/td>\n<\/tr>\n
1<\/td>\nWael<\/td>\n23<\/td>\nCairo<\/td>\n800$<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s write the previous query again and see what happens:\n
INSERT INTO startup\nVALUES(1, ‘Wael’, 23, ‘Cairo’, ‘800$’);<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe see an error telling us:\n
ERROR: \u00a0duplicate key value violates unique constraint “startup_pkey”\nDETAIL: \u00a0Key (id)=(1) already exists.<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThat\u2019s because the id is a primary key and can only contain unique values not duplicates like the id = 1.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIf you made a mistake and enter a negative number for the age like this:\n
INSERT INTO startup\nVALUES(1, ‘Wael’, -23, ‘Cairo’, ‘800$’);<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nYou will get an error:\n
ERROR: \u00a0new row for relation “startup” violates check constraint “startup_age_check”\nDETAIL: \u00a0Failing row contains (1, ‘Wael’, -23, ‘Cairo’, ‘800$’).<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nThat\u2019s because you already made a constraint with a CHECK clause to include only the positive numbers.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s do another query to form the second row:\n
INSERT INTO startup\nVALUES(2, ‘Ragab’, 24, ‘Giza’, ‘900$’);<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nLet\u2019s see what will happen if I do the following to fill the third row:\n
INSERT INTO startup\nVALUES(3, Ali, 24, ‘Giza’, ‘1500$’);<\/span><\/div>\n \n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThere is an error telling us:\n
ERROR: \u00a0column “ali” does not exist<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThat\u2019s because the column \u2018name\u2019 which contains \u2018Ali\u2019 is not appearing as a string so we need to put it between single quotes.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nWhen forming the fourth row:\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tINSERT INTO startup\nVALUES(4, ‘Othman’, 25, ‘Giza’, ‘2000$’);<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s fill the rest of the columns:\n
INSERT INTO startup\nVALUES(5, ‘Waleed’, 26, ‘Cairo’, ‘2100$’);\nINSERT INTO startup\nVALUES(6, ‘Abdo’, 23, ‘Alex’, ‘900$’);\nINSERT INTO startup\nVALUES(7, ‘Moetaz’, 22, ‘Cairo’, ‘2000$’);<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIn the last row, I made a mistake and put the salary 2000$ not 800$. We can fix this by an updating query:\n
UPDATE startup\nSET salary = ‘800$’\nWHERE id = 7;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe UPDATE clause is used followed by the name of the table setting the salary to 800$ for a condition where we do know that it defines the row of interest like the id = 7.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Inserting into specific columns:<\/b><\/h3><\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s try to add another row with specific columns; id and salary values:\n
INSERT INTO startup(\nid, salary)\nVALUES(8, ‘1000$’);<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThis will introduce an error telling us:\n
ERROR: \u00a0null value in column “name” violates not-null constraint\nDETAIL: \u00a0Failing row contains (8, null, null, null, 1000$).<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThis means you must fill the column \u2018name\u2019 because we defined it as NOT NULL which means we can NOT let it empty; it must take value.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSo, we can fix this by adding the name column to the query:\n
INSERT INTO startup(\nid, name, salary)\nVALUES(8, ‘Mohammed’, ‘1000$’);<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

How many rows do exist in a table?<\/b><\/h3><\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe would like to know how many persons in this startup. We do that by counting the number of rows using count(*):\n
SELECT COUNT(*)\nFROM startup;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

\u00a0How many rows with minimum values in a column?<\/b><\/h3><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe would like to know which persons in the startup have the lowest salary and how many of them. Let\u2019s try this query:\n
SELECT *\nFROM startup\nWHERE salary = MIN(salary);<\/span><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nThis will introduce an error which says:\n
ERROR: \u00a0aggregate functions are not allowed in WHERE<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tAggregate functions are like COUNT(), MIN(), MAX(), AVG(), SUM(), etc. which take values in a column as an input and returns a single value (or NULL) . Here MIN() is used after WHERE clause, so we can do that by checking the salary if it equals the minimum value (or not) which we can get from another query, not by aggregating it, like this:\n
SELECT MIN(salary)\nFROM startup;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nBut this will produce illogical value which is 1000$, so what happened?!\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThis is done because of the collation sequence, if you are interested in the ASCII collating orders you can see this\u00a0link<\/a>.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSo, what should we do now?\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tActually, I did something not efficient at the beginning of creating the table which is storing the column salary as varchar. The reason exists at the\u00a0RidFilter<\/i>\u2018s\u00a0answer<\/i>\u00a0of this\u00a0stackoverflow question<\/a>.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tBut believe it or not, I dealt with some data like that. It includes dollar sign stored as varachar so let\u2019s fix it to be able to do some operations on it.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Removing a character from a string:<\/b><\/h3><\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThis problem can be solved by first removing the dollar sign and then converting this varchar to numeric value which can be integer.\n
SELECT REPLACE(salary, ‘$’, ”)\nFROM startup;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThis REPLACE() function will result in the values in salary column without the dollar sign (i.e. replacing the $ by nothing), but beware that this is not edited into the table. So, we need to use it next time we need to operate on it. Also, beware that this column is still string not numeric, so we need to cast it into decimal.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tNow, we can apply the MIN() on the casted replaced values of the salaries:\n
SELECT MIN(\nCAST(\nREPLACE(salary, ‘$’, ”)\nas DECIMAL))\nFROM startup;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe CAST() function converts the new salary column into decimal values.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tRemember, we still see the salary with the dollar sign in the startup table.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tTo make it available in the table without the dollar sign, we use the UPDATE() function:\n
UPDATE startup\nSET salary = REPLACE(salary, ‘$’, ”);<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nWe, now, return to our problem which is finding out the persons inside the startup who have the lowest salary.\n
SELECT *\nFROM startup\nWHERE CAST(salary AS DECIMAL) = 800;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe could use just one query with a subquery instead of the last two separate queries:\n
SELECT *\nFROM startup\nWHERE CAST(\nsalary AS DECIMAL) = (\nSELECT MIN( CAST(salary AS DECIMAL) ) FROM startup );<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe can also count them using COUNT(*):\n
SELECT COUNT(*)\nFROM startup\nWHERE CAST(salary AS DECIMAL) = 800;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe three lowest paid:<\/b>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe would like to get the three lowest paid engineers in the startup. We can do that by first querying all the rows using ORDER BY clause followed by ASC or just ORDER BY which will order the output ascendingly by default.\n
SELECT *\nFROM startup\nORDER BY\nCAST( salary AS DECIMAL );<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tAdding to \u2018LIMIT 3\u2019 will bring the first three corresponding to the rows with the lowest salaries.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe three highest paid:<\/b>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tNotice what we changed!\n
SELECT *\nFROM startup\nORDER BY\nCAST( salary AS DECIMAL ) DESC\nLIMIT 3;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

\u00a0Total salaries:<\/b><\/h3><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s say the CEO wants to know the total cost of the salaries, so we can do that using the aggregate function SUM():\n
SELECT SUM(\nCAST( salary AS DECIMAL )\n) FROM startup;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

\u00a0Adding a column to a table:<\/b><\/h3><\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet\u2019s say, he wants now to hire females, so he would add another column named sex. This can be done using ALTER clause:\n\nALTER TABLE\nstartup\nADD sex char(1);<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe should update each row like that:\n
UPDATE startup\nSET sex = ‘M’\nWHERE id = 1;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

\u00a0Using for loop:<\/b><\/h3><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tOf course, this is a cumbersome if we do it manually. That\u2019s why we should use loops instead.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSQL does not have loops, but it can only be used inside a procedural language function or a \u2018Do\u2019 statement as answered\u00a0here<\/a>:\n
DO\n$do$\nBEGIN\nFOR i IN 2..8 LOOP\nUPDATE startup2 set sex = ‘M’ WHERE id = i;\nEND LOOP;\nEND\n$do$;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

(Kind of) histogram:<\/b><\/h3><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tOne of the possible requirements is to know the frequency of something occurrence. We can get the frequency of the city among the engineers in the startup by counting each row occurrence for each city value. That\u2019s why we use GROUPY BY clause:\n
SELECT city, COUNT(*)\nFROM startup\nGROUP BY city;<\/span><\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThis is like histogram; it shows us how frequent value occurs.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nWe can name any column if we follow it by AS:\n
SELECT city, COUNT(*) AS frequency\nFROM startup\nGROUP BY city;<\/span><\/div>\nI hope you find this article useful.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

If you know some SQL, I hope you find this article useful as well and see some other things might add to your knowledge. You can mostly write queries with SQL. This is almost right because SQL is very intuitive. In this tutorial, you will learn how to create a table, insert values into it, use and understand some data types, use SELECT statements, UPDATE records, use some aggregate functions and more.<\/p>\n","protected":false},"author":621,"featured_media":3649,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"content-type":"","footnotes":""},"categories":[187],"tags":[95],"ppma_author":[3336],"class_list":["post-1890","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bigdata-cloud","tag-big-data-amp-technology"],"authors":[{"term_id":3336,"user_id":621,"is_guest":0,"slug":"ezz-el-din-abdullah","display_name":"Ezz Abdullah","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=mm&r=g","user_url":"","last_name":"Abdullah","first_name":"Ezz","job_title":"","description":"Ezz El Din Abdullah, a former Data Scientist intern, is MATLAB Tutor and teaches such courses as Basics of Mathematics, Numerical Computing, Statistics, and Data Analysis with MATLAB."}],"_links":{"self":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts\/1890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/users\/621"}],"replies":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/comments?post=1890"}],"version-history":[{"count":5,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts\/1890\/revisions"}],"predecessor-version":[{"id":36809,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts\/1890\/revisions\/36809"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/media\/3649"}],"wp:attachment":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/media?parent=1890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/categories?post=1890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/tags?post=1890"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=1890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}