Question

When reading text, you concentrate on the words rather than the punctuation. Computers; however, focus on punctuation before the actual words. Any mistake in punctuation can confuse the computer. More than half of the errors most people make while learning SQL are due to errors in punctuation. What makes this concept even more difficult is that punctuation has additional meaning in SQL that it does not have in the English language.

Instructions

Prepare your responses and items for Part A and Part B in a Microsoft Word document and submit it using the upload instructions. You will use this assignment as the basis for our in-seat discussion so come to class prepared to discuss with the class.

Part 1

Directions: To begin learning more about this topic, do some research to answer the following questions. Your answer should be no more than 5-6 sentences per question.

• What is a reserved word?
• What can you use to handle spaces in words?
• What is case sensitivity? Is it important?
• Is the SQL punctuation different between DBMSs such MySQL, Oracle, and Access? If so, how?

Part 2

Directions: Review the SQL Tutoria, http://www.w3schools.com/sql/default.asp. Links to an external site.To answer the questions below. Your answer should be no more than 5-6 sentences per question.

• Describe how SQL syntax differs between database management systems. Provide specific syntax examples showing these differences.
• Create your first SQL Oracle statement after reviewing the resource link above; everyone's SQL query should be different. Provide the SQL code and describe what data is being retrieved as well as how you would use the information for decision purposes.

• Identify how you might come up with the same data using other SQL syntax.

Answer 1

As discussed in the above question, while reading any particular text, we as humans tend to focus more on the words and meanings of the sentences rather than the punctuation themselves. However, for any mechanical devices like the computer, or similar devices tends to focus more on the punctuation than the words itself. Thus, it is said that the computer programs must be both syntactically and meaningfully correct for compilation and running without any errors. The computer breaks a given instructions into chunks of data based on the given instruction’s punctuation and then solves each of them separately. Finally, it combines them together to get a final result or conclusion. Each punctuation in any given programming language has a specific meaning. This is similar even in case of SQL statements. To write a correct SQL statement one must follow the standards of the SQL language they are using or it might give a completely different meaning.

For example: the ‘*’ generally means ‘multiplicative operator’ in most programming languages. However, it might also be used for declaring a pointer variable in some languages line C++/C#/etc., whereas in SQL it generally means ‘all’. Thus, using a punctuation without an appropriate meaning in the statements wouldn’t let it compile and as a result it would result in an error.

Part 1:

1. Reserved Words: A reserved word in any programming language is a specific set of words that has a specific meaning in that particular programming language and can not be modified by the programmer. For example: the keywords public, private, int, double, etc. are reserved words in C++/C#/etc. and can not be modified by the programmers. Reserved words are quite similar even in SQL. Certain words in SQL are reserved for the system and can not be used by any programmer as an identifier i.e. name as a table, AS alias, etc. they can only be used for their indented purposes only. E.g.: SELECT, FROM, AS, etc.

E.g.: SELECT * FROM <table name>;

2. Space in words: To handle spaces in words we must use the special characters i.e.: a single quote (‘ ’) . Any string (or varchar or varchar2 as in case of SQL statements) that might have a space in between can be written within a single quote. As such the space can be retained within the word as indented. This is specially required when the column data is of type string (varchar or varchar2 as in case of SQL).

E.g.: “THIS IS A STRING”

c)    Case Sensitivity: Case sensitivity of any programming language means that, the particular programming language    treats the same words written in upper and lower case as different. The two words “SQL” and “sql” are different. Case sensitivity is essential as in most cases the passwords are stored in databases. As such distinction between upper and lower case is required.

d)    MySQL vs Oracle vs Access: Though syntactically all three of MySQL, Oracle and MS Access are quite similar but syntactically they are quite different in may aspects. For example, let us take example of an cartesian product often called cross product, for two tables ship and shipyard.

MySQL: select * from ship cross join shipyard

Oracle: select * from ship, shipyard

Access: Technically there is no cross join in MS Access. However, the same objective may be achieved by doing:

select * from ship ship-name, shipyard shipyard-name

(ship-name and shipyard-name are references of the two table.)

There are multiple cases where though the same statement is punctually different in all 3 SQL languages.

Part 2:

1. SQL statements differ greatly between the different database management systems. There are many instances where the same statement might be written differently in different database management system because of a punctuation difference, for example: as seen in case of cartesian products in MySQL and Oracle. We need to use the cross product. In MySQL we use the keyword “cross join” between the table names, however in case of oracle we need not do so. A simple “,” in between the two table names will suffice. Though the end result will be the same but the SQL statements are different. There might be also cases where a specific keyword might be present in one while missing in another, hence the SQL statements for the same purpose becomes different.

MySQL: select * from ship cross join shipyard

Oracle: select * from ship, shipyard

2. Create Table:

Now let us create a table customer having customer id, name and location. Then insert some in it and extract data from the table based on some conditions.

Thus, table schema would be something like this: customer (id, name, location)

SQL statement to create the table where table name is customer, id is of type integer and name, location is of type varchar2.

create table customer (id number not null, name varchar2(10), location varchar2(10));

Thus, we’re defining in here the table name followed by the column names and the type of data they might contain. By adding not null we mean that that particular column must contain any data and cannot be null.

Now let us assume that the particular table that we so create has the following data contained in it.

ID	NAME	LOCATION
100	TOM	LONDON
101	RICK	NEW YORK
102	JACK	LONDON
103	RITZ	OKINAWA

Thus, the SQL statement in oracle to retrieve the data from the table is:

select * from customer;

OUTPUT:

ID	NAME	LOCATION
100	TOM	LONDON
101	RICK	NEW YORK
102	JACK	LONDON
103	RITZ	OKINAWA

In here the * refers to all the data contained in all the columns. As there is no condition to select which rows it’ll print all rows. Basically, we’re saying oracle to print the entire data available in the table.

Now if we require to print the data based on any particular condition, we will simply use the conditional keyword called “where” and add the conditions after it. Let us print the data for all customers whose location is London. Thus, the statement will be something like this: select * from customer where location = ‘LONDON’;

OUTPUT:

ID	NAME	LOCATION
100	TOM	LONDON
102	JACK	LONDON

3. Now please note that * means printing data in all the columns. Thus, the statement

select * from customer; can be written as select ID, NAME, LOCATION from customer;

This is because only three columns are present. Giving * or specifying all three of them will give the same result. Similarly,

the statement select * from customer where location = ‘LONDON’;

can be written as select ID, NAME, LOCATION from customer where location = ‘LONDON’;

Here too giving * to print the data in all three columns and specifying all three columns will give the same result.Thus coming to the same result using another SQL statement.