3. Given the table – LIBRARY
Give the output of following SQL commands on the basis of table Library.
(i) SELECT UPPER(Title) FROM Library WHERE Price < 150;
Answer: Output
| UPPER(Title) |
| Computer Studies |
| Dbase dummies |
| Mastering Foxpro |
| Basic for beginners |
(ii) SELECT CONCAT(Author, Type) FROM Library WHERE Qty < 150;
Answer: Output
| CONCAT(Author, Type) |
| FrenchFND |
| SeigalDBMS |
| CowartOS |
(iii) SELECT MOD(Qty, 4) FROM Library;
Answer: Output
| MOD(Qty, 4) |
| 0 |
| 2 |
| 0 |
| 1 |
| 3 |
| 3 |
| 2 |
| 3 |
| 3 |
| 1 |
4. Write a query to show the current date and time.
Answer: SELECT NOW();
OR
SELECT SYSDATE()
5. Write a query to show the city of user with first name as ‘MARK’.
Answer: SELECT CITY FROM ADDRESS WHERE FirstName = ‘MARK’;
6. Show via query how many days remain until Christmas. Round fractional days up using the numeric function ROUND.
Answer: SELECT ROUND(DATEDIFF(‘2022-12-25’, CURDATE))AS ‘DAYS LEFT IN CHRISTMAS’;
7. Write a query against table PAYDAY that will show the number of day (CYCLEDATE column) and the last day of the month (LAST DAY function).
Format your results as below:
| Month | Days between payday and first days of next month |
| January | 16 |
| February | 13 |
| March | 16 |
8. Write a query against the EMPL table to show the names of all employees concatenated with their jobtypes.
9. Write a query against the ADDRESS table to show the names (first name, last name) and phones of all persons concatenated in following form :
TinaSeth23456789
MoradK. 22211898
10. Write a query against the ADDRESS table to select a list of names and phone numbers. The output should match these requirements:
- The name column should contain both the first and last names with a blank space between them. Use the string concatenation.
- The second column will contain the phone number.
- Phone number should be in the format (999) 999-9999. Use the SUBSTR function and CONCAT.
- Order the query by last name then first name.
