Register Login





|

Top Links: >> 80. Technology >> Internet Technology Summit Program >> 2. Java and Databases
Current Topic: 2.1. SQL
Sub-Topics: 2.1.1.SQL Query with wildcard characters and REPLACE function
-- Scroll to check for more content below...
You have a privilege to create a quiz (QnA) related to this subject and obtain creativity score...
All RDBMS must speak SQL. What is the main structure of SQL?
SQL consider three categories of instructions.

Data Manipulation Language (DML) includes the following instructions: SELECT, INSERT, UPDATE, DELETE.
The SELECT instruction does not change data, just retrieves the data. Insert, Update and Delete instructions change data.
For example, select * from UserProfile
This instruction will retrieve all records from the UserProfile table. Each retrieved record will include all the fields of the record.

Generally speaking the format for the SELECT instruction is:
SELECT column1, column2....columnN
FROM table_names
WHERE CONDITION [order by columnName]

For example:
SELECT LastName, Login from UserProfile where FirstName=’John’ order by LastName;
This instruction will retrieve all last names and login names of users with the first name equals ‘John’ and will deliver these names sorted by the last name.

Now, let us try less precise condition. Instead of the equals sign, we will use the like keyword with the wildcard %.
SELECT LastName, Login from UserProfile where FirstName like ’J%’ order by LastName;
This instruction will retrieve all last names and login names of users with the first name starts with the ‘J’ character and will deliver these names sorted by the last name.

To add another record to a table SQL uses the INSERT instruction with the following format:
INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);

And even simpler format, when we place values in the same order as they are located in the record:
INSERT INTO table_name VALUES ( value1, value2....valueN);
For example:
INSERT INTO UserProfile VALUES ( ‘jeff@javaschool.com’, ‘Jeff’, ‘Trainer’);
This instruction will add another record to the UserProfile table.

To update a record in a table SQL uses the UPDATE instruction with the following format:
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];

For example:
UPDATE UserProfile SET FirstName=’John’ WHERE LastName=’Trainer’;
This instruction will change the record where the LastName equals ‘Trainer’ and replace the value of the FirstName ‘Jeff’ with ‘John’.

To delete a record SQL uses the DELETE instruction with the following format:
DELETE FROM table_name
[WHERE CONDITION];

For example:
DELETE from UserProfile WHERE FirstName=’John’;
This instruction will delete all records with the FirstName equals ‘John’;

Conditions in SQL statements can be simple, such as in the examples above, or more complex.
A condition can include logical connectors AND or OR.
A condition not necessarily must have equals sign = but can be less strict using the LIKE keyword.
For example:
Was it clear so far? Highlight the text in question Or


Select * from Employee where lastName like ‘St%’ and salary > 100000;

This example will return records from the Employee table with the last name like Stuart, Stipper, etc. starting with St.
The percentage sign is the wild card in SQL.
But there is another condition, which will filter down the records and only those with the salary > 100000 will be retrieved.


SQL has many built-in functions, such as: COUNT(), MAX(), MIN(), SUM(), etc.
For example:
SELECT COUNT(*) from UserProfile WHERE FirstName = ‘John’; -- counts a number of records with the first name equals John.

Data Definition Language (DDL) uses the following instructions to define data structures: CREATE, DROP, etc.
For Example, Create table Accounts;
This instruction will create a new table Accounts.
Or
Drop table Accounts;
This instruction will delete the table Accounts.
Or
Create database ITS;
This instruction will create a new database ITS.

Data Control Language (DCL) allows database administrators to GRANT or REVOKE privileges for specific users on specific data objects.
For example:
GRANT CONNECT TO Joe; -- Joe can connect to databases
GRANT SELECT ON ITS.UserProfile TO Joe; -- Joe has a privilege to SELECT data from the ITS database, UserProfile table

Notes:
1. Not everyone has privileges to use DML, DDL and DCL. Developers mostly use DML and limited DDL. Usually Database Administrators (DBA) configure databases and have privileges to use DCL. When application needs to access a database, DBA associates this application with the name of a user specifically created by DBA for this purpose.
2. SQL is not case sensitive to SQL keywords. At the same time data can be case sensitive if configured that way in the database.

Data Types

Data Tables have records with data fields. We have to define data types for table columns when we create a new table. Data fields can be numeric or text or they can represent date and time. Data types have different internal representation for different databases and even different data type names.

For example, text fields with relatively small number of characters can be called varchar for many databases. But if a table column can have more than 4000 characters, in Oracle we can assign NCLOB data type and in MySQL we call it text data type. And even data types with the same name can be mapped differently to Java data types. For example, timestamp in Oracle can be mapped to java.sql.Timestamp. But the timestamp in MS SQL Server is mapped to byte[]. There are good hints on data mapping provided in the links to read on the Assignments. The bottom line: do not assume, but check SQL data types and their mapping to Java data types for a specific database you use in your project.

Assignments:
1. Read on data mapping:
1.1. http://www.service-architecture.com/articles/database/mapping_sql_and_java_data_types.html
1.2. More specific mapping by Microsoft: https://msdn.microsoft.com/en-us/library/ms378878%28v=sql.110%29.aspx
1.3. Recommended mapping reference from Oracle

2. Read more on SQL and RDBMS:
http://www.tutorialspoint.com/sql/sql-rdbms-concepts.htm
www.sqlbolt.com
3. Create Design Specification document describing a business goal and related design with the need for a database with two-three tables.
4. Provide a one-page section with several SQL instructions related to the design.
5. Include in the Design Specification the need for calculating MAX and SUM for one of the table columns, for example the SALARY column in the PAYROLL data table.
6. Provide SQL statements using the MAX and SUM SQL functions.
7. Then Upload your MS Word document.
8. Add at least one QnA related to the subject.
Questions and Answers (QnA): QnA1 | QnA2 | QnA3 | QnA4 | QnA5 | QnA6 | QnA7 | QnA8
We offer a fun way to share your experience and be rewarded.
You create a puzzle - quiz with a good question and several answers, one correct and several wrong ones.
The question as well as answers can include text, links, video.
This is your creation, completely up to your taste, humor, and of course your knowledge.
If there is an existing quiz above, you need first to solve the puzzles and evaluate the quality (at the bottom of every quiz).
After finishing the existing quiz you will be able to create your own. The best puzzles will be rewarded!
We invite you to create your own questions and answers (QnA) to increase your rank and win the Top Creativity Prize!

Topic Graph | Check Your Progress | Propose QnA | Have a question or comments for open discussion?

Have a suggestion? - shoot an email
Looking for something special? - Talk to AI
Read: IT of the future: AI and Semantic Cloud Architecture | Fixing Education
Do you want to move from theory to practice and become a magician? Learn and work with us at Internet Technology University (ITU) - JavaSchool.com.

Technology that we offer and How this works: English | Spanish | Russian | French

Internet Technology University | JavaSchool.com | Copyrights © Since 1997 | All Rights Reserved
Patents: US10956676, US7032006, US7774751, US7966093, US8051026, US8863234
Including conversational semantic decision support systems (CSDS) and bringing us closer to The message from 2040
Privacy Policy