Introduction to Transact-SQL (T-SQL)
Construct and execute INSERT SQL statements to add the sample data in the following tables to the Customer and Address tables in the HandsOnOne database:
Additional Sample Data for the Address Table
Construct and execute a SQL query to list all customers with their corresponding cities and states. The list should be sorted in ascending numerical order by ZIP code, followed by customer name alphabetically. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
3. Construct and execute a SQL Query to list the Street, City, State, and Zip Code of all addresses that do not have a customer associated with them. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
4. Construct and execute a SQL query to count the number of customers in each state. The list should be ordered by the number of customers in descending order, then by zip code in ascending order. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
5. Construct and execute a SQL query to change Alice Harper’s address to 320 21st St, Billing, MT, 59101. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
6.Construct and execute a SQL query to list the full names of all customers who have Harper in their name. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
SQL Programming can be both a challenging task and fun. Programmers with a background in old-fashioned programming languages, for example, Java, C, C++, and Visual Basic mostly find it tough to adjust to the set-based mindset. Even experienced SQL Programmers and DBAs can get stuck in one of the many downsides of the SQL language. I have faced the challenge in my assignment and realized mastering the basics takes time, and even then some errors are hard to spot.
In writing the SQL statement the most challenging part was programming the joins. Joins can be challenging, especially when you keep on getting errors. Since joins involves joining more than on table to come up with a desired outcome, it makes it a challenging task since the query statements are long. The statements are also programmed differently from the traditional way of SELECT query Statements.
Challenge number two was debugging errors in my queries. The challenge becomes difficult to solve when the outcome of a query is wrong and you cannot understand why. Writing a proper syntax is essential to avoid such errors as I have come to learn from this assignment. There are also semantic errors that occur due to writing wrong table names or column names.
Despite the challenges faced during SQL programming, creating TABLES in SQL and select statements are some of the easiest jobs. The statements for creating tables are straight forward. The select statements are also easy to understand since they are directly fetched from everyday English statements. For example, if I need to know information about John, who is a student, all I have to write is a statement as this, SELECT FROM TABLE student WHERE name = John. Another example is creating a database. For example, if I want to create a table named Student, all I have to do is to write CREATE TABLE student as my statement.
In conclusion, SQL stands for Structured Query Language, is easy to learn and there are many tutorials available online. In this assignment I have present briefly the overview of challenges for Standard Query Language, I have also analyzed the benefits and risk of accepting SQL. There are advantages of embracing SQL such as operational costs and lower capitalized, improved swiftness and manageability and higher convenience. There are many errors that can occur when writing difficult SQL statements, but frequently the user can easily handle those bugs. In upcoming work, this assignment can explore patterns for SQL queries in broader detail such as analyzing SQL queries from real schemes.
