SQL Challenges for Modifying Databases
SQL Challenges from Khan Academy Allow for Effective Learning on Modification of Databases
Structured Query Language or SQL is a “domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS)” (definition from https://en.wikipedia.org/wiki/SQL). SQL is a tool utilized today by data professionals to access specific important data from a database with a few lines of computer code. The data can be viewed simplistically and manipulated in groups for the purpose of deriving understandable and cohesive formatting of data. In my blog today, I plan to expand on the use cases for SQL to discuss some complex methods of utilizing SQL on databases that involve not only the viewing and manipulation of data, but more specifically, the modification of data in databases. Through modification of data with SQL, data that exists in databases is not only utilized and organized, but it is changed from its original structure. SQL modifications are to be taken seriously because database data morphs into something different than it originally was before the application of SQL modification commands.
To practice effectively modifying data employing SQL and minimize modification mistakes with SQL, I imported two challenges from the Khan Academy website to view and improve skills on modification of data with use of SQL. The exercises are not my own creation, but were obtained from the Khan Academy website. However, the solutions are inspired by me from the challenges transferred from the Khan Academy website. The modification challenges with problems and solutions are below. I wrote out explanations of the solutions to help enhance effective comprehension on modifying data employing SQL.
Challenge 1: Dynamic Documents
Dynamic Documents Step 1:
We’ve created a database for a documents app, with rows for each document with its title, content, and author. In this first step, use UPDATE to change the author to 'Jackie Draper' for all rows where it's currently 'Jackie Paper'. Then reselect all the rows to make sure the table changed like you expected.
Dynamic Documents Solution 1:
Dynamic Documents Solution 1 Query Results:
To solve the first part of the Dynamic Solutions problem, I began by writing a “SELECT * documents;” statement to view the documents data table as it existed unchanged in the database. Then, I wrote an “UPDATE documents SET author= “Jackie Draper” WHERE author= “Jackie Paper;” ”statement to change the author title of “Jackie Paper” to “Jackie Draper” every place where “Jackie Paper” was originally included in the data table. I added another “SELECT * documents;” statement at the end to see the documents table as it existed in the query results with the updated data. The UPDATE command in SQL updates a data column value with a new data column value modifying the data table.
Dynamic Documents Step 2:
Now you’ll delete a row being very careful not to delete all the rows. Only delete the row where the title is ‘Things I’m Afraid Of”. Then reselect all the rows to make sure the table changed like you expected.
Dynamic Documents Solution 2:
Dynamic Documents Solution 2 Query Results:
In order to delete the row where the title was “Things I’m Afraid Of” I composed a “DELETE FROM documents WHERE id= 5;” statement because the row with “Things I’m Afraid Of” as the title was the row where the id was 5. To check the updated table with the query results, I scribed a “SELECT * FROM documents;” statement. The DELETE command in SQL deletes a row of data from a data table, thus changing the data representation. The WHERE statement is important to a delete statement because the WHERE statement guarantees that only a specified row or rows gets deleted.
Challenge 2: Clothing Alterations
Clothing Alterations 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.
Clothing Alterations Solution 1:
Clothing Alterations Solution 1 Query Results:
For this problem, I devised an “ALTER TABLE clothes ADD price REAL;” statement to add a price column to the existing table. The ALTER TABLE command in SQL is used to add, delete, or modify columns in an existing table. The ADD command adds a price column to the table. The “price REAL” part of the initial statement makes the data type of the price column to include any real number. I comprised a “SELECT * FROM clothes;” statement to observe the clothes table with the new price column added.
Clothing Alterations 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 done, do another SELECT of all the rows to check that it worked as expected.
Clothing Alterations Solution 2:
Clothing Alterations Solution 2 Query Results:
The rows of the clothes table can be updated so that item 1 should be 10 dollars, item 2 should be 20 dollars, and item 3 should be 30 dollars with the following “UPDATE clothes SET price= “10” WHERE id= 1; UPDATE clothes SET price = “20” WHERE id= 2; UPDATE clothes SET price= “30” WHERE id = 3;” code. The price is added to each corresponding row location with the “SET price” and “WHERE” parts of the code. The “SET price” code sets the price and the “WHERE” initializes the location for the data. The “SELECT * FROM clothes;” statement included at the end provides the updated clothes table with the price values included in the price columns on the table. The dress is now 10 dollars, the pants are now 20 dollars, and the blazer is now 30 dollars on the table. The table has been appropriately changed.
SQL Challenges on Modifying Data Solved and Explained
SQL is a programming language that is implemented to comprehend and assess complicated data at a more basic foundational level. Not only is SQL able to organize and manipulate data in databases, but SQL can change the configurations of data in databases as well. Through modifications of data in databases by executing SQL modification commands, data is transformed into something different from what it once was. Modifying data in SQL is an action to take with caution because data can be greatly altered from its original format utilizing SQL modification statements. To help people learn how to better modify data using SQL and mitigate issues from performing SQL modification commands, I produced two SQL challenges from the Khan Academy website with solutions in this blog. The purpose of presenting these challenges is to allow people to visualize SQL modification commands and improve the usages of SQL modification commands in their work. I hope the problems provided insight into best practices for applying SQL modifications to data in databases. Good luck and be careful with your future adventures in modifying data with SQL!
Resources:
