Step 5. Query the Loaded Data¶
You can query the data loaded in the emp_basic table using standard SQL and any supported
functions and
operators
.
You can also manipulate the data, such as updating the loaded data or inserting more data, using standard DML commands.
Retrieve All Data¶
Return all rows and columns from the table:
SELECT * FROM emp_basic;
The following is a partial result:
+------------+--------------+---------------------------+-----------------------------+--------------------+------------+
| FIRST_NAME | LAST_NAME | EMAIL | STREETADDRESS | CITY | START_DATE |
|------------+--------------+---------------------------+-----------------------------+--------------------+------------|
| Arlene | Davidovits | adavidovitsk@sf_tuts.com | 7571 New Castle Circle | Meniko | 2017-05-03 |
| Violette | Shermore | vshermorel@sf_tuts.com | 899 Merchant Center | Troitsk | 2017-01-19 |
| Ron | Mattys | rmattysm@sf_tuts.com | 423 Lien Pass | Bayaguana | 2017-11-15 |
...
...
...
| Carson | Bedder | cbedderh@sf_tuts.co.au | 71 Clyde Gallagher Place | Leninskoye | 2017-03-29 |
| Dana | Avory | davoryi@sf_tuts.com | 2 Holy Cross Pass | Wenlin | 2017-05-11 |
| Ronny | Talmadge | rtalmadgej@sf_tuts.co.uk | 588 Chinook Street | Yawata | 2017-06-02 |
+------------+--------------+---------------------------+-----------------------------+--------------------+------------+
Insert Additional Data Rows¶
In addition to loading data from staged files into a table, you can insert rows directly into a table using the INSERT DML command.
For example, to insert two additional rows into the table:
INSERT INTO emp_basic VALUES
('Clementine','Adamou','cadamou@sf_tuts.com','10510 Sachs Road','Klenak','2017-9-22') ,
('Marlowe','De Anesy','madamouc@sf_tuts.co.uk','36768 Northfield Plaza','Fangshan','2017-1-26');
Query Rows Based on Email Address¶
Return a list of email addresses with the United Kingdom domain names using the LIKE function:
SELECT email FROM emp_basic WHERE email LIKE '%.uk';
The following an example result:
+--------------------------+
| EMAIL |
|--------------------------|
| gbassfordo@sf_tuts.co.uk |
| rtalmadgej@sf_tuts.co.uk |
| madamouc@sf_tuts.co.uk |
+--------------------------+
Query Rows Based on Start Date¶
Add 90 days to employee start dates using the DATEADD function to calculate when certain employee benefits might start. Filter the list by employees whose start date occurred earlier than January 1, 2017:
SELECT first_name, last_name, DATEADD('day',90,start_date) FROM emp_basic WHERE start_date <= '2017-01-01';
The following is an example result:
+------------+-----------+------------------------------+
| FIRST_NAME | LAST_NAME | DATEADD('DAY',90,START_DATE) |
|------------+-----------+------------------------------|
| Granger | Bassford | 2017-03-30 |
| Catherin | Devereu | 2017-03-17 |
| Cesar | Hovie | 2017-03-21 |
| Wallis | Sizey | 2017-03-30 |
+------------+-----------+------------------------------+