Shin Nix

In this lesson, we will look at stored procedures and how it makes the job of querying to a database more efficient. Imagine that  a person is not that well-versed in creating SQL queries but this person needs information from the database to make informed decisions relating to his job. Well, fret not. For another geeky nerdy expert person could make the query for that person and all he has to do is to write a string of code that is not as lengthy as the stored procedure. In this case, let's say we want to know the average salary of a salesman from a company database. But we are not that well-versed with SQL. What our database developer or administrator can do is to create a stored procedure that will automate the retrieval of such information. In the database developer or administrators eyes, this is what he sees: Next, the developer / administrator will modify the stored procedure. We do the following modifications to the stored procedure: Now, the administrator of the database c...

I am now on SQL Advanced queries and the lesson focused on temp tables. As the name suggests, temp tables allow us to create tables outside of the database which is stored somewhere in memory. What I find interesting about this lesson is the fact that we can extract a table from our database and store it on our temp table for FASTER data manipulation and analysis LOCALLY. I could imagine a scenario wherein I am working from home and I am told to work on a specific table on the database that is situated miles away but can be accessed using the internet. Now, instead of wasting internet resources every time I get data and execute SQL queries to and from the database, I can now extract the tables I need from the database, work on it it locally and then be able to report the needed insights and visualizations.

I am now on what Alex the Analyst would call the advanced SQL queries. First off, the Common Table Expression or CTEs. Based on what I understood in the video, CTEs is similar to a function or class in Python which you can call out. It is just how I felt when I first encountered this expression. Now, in this particular case, I tried to replicate what Alex is doing but also not copying the codes that he is using. I am just simply trying to understand what the expression that was used (which starts with WITH) and then observe how he used the said expression. This was my code. Now this code, resulted in the following error. Msg 8156, Level 16, State 1, Line 84 The column 'EmployeeID' was specified multiple times for 'CTE_Employee'. What does someone do in this day and age if we encounter something that bogs us down? We go to the internet especially to AI tools to aid us out. And, apparently, these tools will really come in handy. It stated that the error occurred because I...

I just would like to pass by and state that: WHERE comes first before GROUP BY while GROUP BY comes first before HAVING. We cannot use WHERE if the condition is an AGGREGATE function i.e. COUNT, AVG, MAX. In these cases, we use HAVING. Also, when using CASE statement, bear in mind that the result of a case statement is ANOTHER COLUMN. In other words, it is like creating a new column in MS Excel while using an IF-ELSE statement to populate the contents of that column. Since it is another column, it is written as one of the columns that needs to be on the output of the select statement. For example, Notice that we put a comma before we used the case statement and we named the column output of the case statement as IncreasedSalary. Here is also a trick when one wants to delete data in the table. Deleting values in a table is a very crucial thing that must be taken with caution because one misstep and one can delete the whole table. So to do away with this and ensure that you are deleting ...

What's up Nixers! After finishing the Power BI tutorial by Alex the Analyst, I decided to embark on learning SQL once again. A sort of a review of what I have already learned in Khan Academy. I am already in 5 of the 17 videos in this playlist. The reason why I decided on this next step is to test my knowledge of SQL following what I have learned in Khan Academy. I thought to myself that if I can use my SQL skills to do what he instructs before looking at the code he uses, then I will have better confidence moving forward. If not, I will have a serious look at what I lack and improve on those things. The things that I noticed is that Alex uses SQL Server Management Studio (SSMS) while Khan Academy uses SQLlite.js. I learned the difference the hard way as I committed errors in the codes that I used. In SQLlite, the use of 'TEXT' as a data type was now changed to 'varchar(size)'  is prevalently used in SQLServer. Also, when assigning values, the use of single-quotatio...

  Challenge: Clothing Alterations Given data: CREATE TABLE clothes (     id INTEGER PRIMARY KEY AUTOINCREMENT,     type TEXT,     design TEXT);      INSERT INTO clothes (type, design)     VALUES ("dress", "pink polka dots"); INSERT INTO clothes (type, design)     VALUES ("pants", "rainbow tie-dye"); INSERT INTO clothes (type, design)     VALUES ("blazer", "black sequin"); Step 1 We've created a database of clothes, and decided we need a price column. Use ALTER to add a 'price' column to the table. Then select all the columns in each row to see what your table looks like now. Code: ALTER TABLE clothes ADD price REAL; SELECT * FROM clothes; Query Results: id type design price 1 dress pink polka dots NULL 2 pants rainbow tie-dye NULL 3 blazer black sequin NULL Step 2 Now assign each item a price, using UPDATE - item 1 should be 10 dollars, item 2 should be 20 dollars, item 3 should be 30 dollars. When you're do...

So far, we are only "reading" from a given database or table using the SELECT command of SQL. In today's lesson, we will now start "writing" into a given database using the UPDATE and DELETE commands. Challenge: Dynamic Documents Given data: CREATE table documents (     id INTEGER PRIMARY KEY AUTOINCREMENT,     title TEXT,     content TEXT,     author TEXT);      INSERT INTO documents (author, title, content)     VALUES ("Puff T.M. Dragon", "Fancy Stuff", "Ceiling wax, dragon wings, etc."); INSERT INTO documents (author, title, content)     VALUES ("Puff T.M. Dragon", "Living Things", "They're located in the left ear, you know."); INSERT INTO documents (author, title, content)     VALUES ("Jackie Paper", "Pirate Recipes", "Cherry pie, apple pie, blueberry pie."); INSERT INTO documents (author, title, content)     VALUES ("Jackie Paper", "Boat Supplies...

Challenge: Famous People Instructions: In this project, you’re going to make your own table with some small set of “famous people”, then make more tables about things they do and join those to create nice human-readable lists. For example, here are types of famous people and the questions your data could answer: Movie stars: What movies are they in? Are they married to each other? Singers: What songs did they write? Where are they from? Authors: What books did they write? Fictional characters: How are they related to other characters? What books do they show up in? Created Tables: /* Create table about the people and what they do here */ CREATE TABLE famous_people (id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT); INSERT INTO famous_people(first_name,last_name) VALUES ("Amber", "Heard"); INSERT INTO famous_people(first_name,last_name) VALUES ("Johnny", "Depp"); INSERT INTO famous_people(first_name,last_name) VALUES ("An...

Challenge: FriendBook Given: CREATE TABLE persons (     id INTEGER PRIMARY KEY AUTOINCREMENT,     fullname TEXT,     age INTEGER);      INSERT INTO persons (fullname, age) VALUES ("Bobby McBobbyFace", "12"); INSERT INTO persons (fullname, age) VALUES ("Lucy BoBucie", "25"); INSERT INTO persons (fullname, age) VALUES ("Banana FoFanna", "14"); INSERT INTO persons (fullname, age) VALUES ("Shish Kabob", "20"); INSERT INTO persons (fullname, age) VALUES ("Fluffy Sparkles", "8"); CREATE table hobbies (     id INTEGER PRIMARY KEY AUTOINCREMENT,     person_id INTEGER,     name TEXT);      INSERT INTO hobbies (person_id, name) VALUES (1, "drawing"); INSERT INTO hobbies (person_id, name) VALUES (1, "coding"); INSERT INTO hobbies (person_id, name) VALUES (2, "dancing"); INSERT INTO hobbies (person_id, name) VALUES (2, "coding"); INSERT INTO hobbies (person_id...

Challenge: Sequels in SQL Given data: CREATE TABLE movies (id INTEGER PRIMARY KEY AUTOINCREMENT,     title TEXT,     released INTEGER,     sequel_id INTEGER); INSERT INTO movies      VALUES (1, "Harry Potter and the Philosopher's Stone", 2001, 2); INSERT INTO movies      VALUES (2, "Harry Potter and the Chamber of Secrets", 2002, 3); INSERT INTO movies      VALUES (3, "Harry Potter and the Prisoner of Azkaban", 2004, 4); INSERT INTO movies      VALUES (4, "Harry Potter and the Goblet of Fire", 2005, 5); INSERT INTO movies      VALUES (5, "Harry Potter and the Order of the Phoenix ", 2007, 6); INSERT INTO movies      VALUES (6, "Harry Potter and the Half-Blood Prince", 2009, 7); INSERT INTO movies      VALUES (7, "Harry Potter and the Deathly Hallows – Part 1", 2010, 8); INSERT INTO movies      VALUES (8, "Harry Potter and the Deathly Hallo...