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:

--

--

--

Flatiron School Alum

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Introducing Chatbots for Glip Developers

How Does Alibaba Ensure the Performance of System Components in a 10,000-node Kubernetes Cluster?

What is TOGAF? Part 4 — Phase E

How to pass environment variables at building time in an Angular application using .env files

Adding Google OAuth 2.0 to your Django project the easy way

Lessons in the road to transform to Agile

How to Remove Occurrences of Elements From a List After Their ‘n’th Occurrence

Build + Buy: How custom development adds value to COTS

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jmstipanowich

Jmstipanowich

Flatiron School Alum

More from Medium

SQL by steps #3 : SELECT

SQL Join Challenges for Improving SQL Skills

8 Week SQL Challenge #3 — Foodie-Fi

SQL vs PL/SQL