SQL Tutorials - 4
.png)
8 View
8.1 Creating View
8.2 Deleting View
9 Stored Procedure
10 Date Time Functions (36 Functions)
8 View
Views in SQL are kind of virtual tables. A view is a query that is stored in the database and returns the result set of the query in which it is defined. We can create a view by selecting fields from one or more tables present in the database. a simple view never stores data, merely it fetches the results of the query in which it is defined.
View restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more. views can provide us to handle security issues. Also user can be granted authorization to see specific data through the views so we don’t have to struggle to give permissions to users for multiple-tables.
We will be using rainfall data which we have used in tutorial 3 for demonstration
8.1 Creating View
CREATE VIEW Rainfall_View AS
SELECT STATE_UT_NAME, DISTRICT, ANNUAL
FROM rainfall
WHERE ANNUAL < 5000;
To see Data in View we can use following query
SELECT * FROM rainfall_view;
8.2 Deleting View
DROP VIEW Rainfall_View;
You can use other DML queries and clause with ALTER keyword on the View in a similar way when working with actual table.
9 Stored Procedure
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
SQL query to create stored procedure -
CREATE PROCEDURE 'rainfall_data'
AS
BEGIN
SELECT* FROM rainfall;
END
SQL Query to call Stored procedure -
CALL GET rainfall_data;
10 Date Time Function
10.1 CURDATE(): It returns the current date.
Syntax: SELECT CURDATE()
Output: 2018-07-16
10.2 CURRENT_DATE(): It returns the current date.
Syntax: SELECT CURRENT_DATE();
Output: 2018-07-16
10.3 CURRENT_TIME(): It returns the current time.
Syntax: SELECT CURRENT_TIME();
Output: 02:53:15
10.4 CURRENT_TIMESTAMP(): It returns the current date and time.
Syntax: SELECT CURRENT_TIMESTAMP();
Output: 2018-07-16 02:53:21
10.5 CURTIME(): It returns the current time.
Syntax: SELECT CURTIME();
Output: 02:53:28
10.6 DATE(): It extracts the date value from a date or date time expression.
Syntax: SELECT DATE("2017-06-15");
Output: 2017-06-15
10.7 DATEDIFF(): It returns the difference in days between two date values.
Syntax: SELECT DATEDIFF("2017-06-25", "2017-06-15");
Output: 10
10.8 DATE_ADD(): It returns a date after a certain time/date interval has been added.
Syntax: SELECT DATE_ADD("2018-07-16", INTERVAL 10 DAY);
Output: 2018-07-16
10.9 DATE_SUB(): It returns a date after a certain time/date interval has been subtracted.
Syntax: SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);
Output: 2018-07-16
10.10 DAY(): It returns the day portion of a date value.
Syntax: SELECT DAY("2018-07-16");
Output: 16
10.11 DAYNAME(): It returns the weekday name for a date.
Syntax: SELECT DAYNAME('2008-05-15');
Output: Thursday
10.12 DAYOFMONTH(): It returns the day portion of a date value.
Syntax: SELECT DAYOFMONTH('2018-07-16');
Output: 16
10.13 DAYWEEK(): It returns the weekday index for a date value.
Syntax: SELECT WEEKDAY("2018-07-16");
Output: 0
10.14 DAYOFYEAR(): It returns the day of the year for a date value.
Syntax: SELECT DAYOFYEAR("2018-07-16");
Output: 197
10.15 EXTRACT(): It extracts parts from a date.
Syntax: SELECT EXTRACT(MONTH FROM "2018-07-16");
Output: 7
10.16 HOUR(): It returns the hour portion of a date value.
Syntax: SELECT HOUR("2018-07-16 09:34:00");
Output: 9
10.17 LAST_DAY(): It returns the last day of the month for a given date.
Syntax: SELECT LAST_DAY('2018-07-16');
Output: 2018-07-31
10.18 LOCALTIME(): It returns the current date and time.
Syntax: SELECT LOCALTIME();
Output: 2018-07-16 02:56:42
10.19 MICROSECOND(): It returns the microsecond portion of a date value.
Syntax: SELECT MICROSECOND("2018-07-18 09:12:00.000345");
Output: 345
10.20 MINUTE(): It returns the minute portion of a date value.
Syntax: SELECT MINUTE("2018-07-18 09:12:00");
Output: 12
10.21 MONTH(): It returns the month portion of a date value.
Syntax: SELECT MONTH ('2018/07/18')AS MONTH;
Output: 7
10.22 MONTHNAME(): It returns the full month name for a date.
Syntax: SELECT MONTHNAME("2018/07/18");
Output: JULY
10.23 NOW(): It returns the current date and time.
Syntax: SELECT NOW();
Output: 2018-07-18 09:14:32
10.24 PERIOD_DIFF(): It returns the difference in months between two periods.
Syntax: SELECT PERIOD_DIFF(201810, 201802);
Output: 8
10.25 QUARTER(): It returns the quarter portion of a date value.
Syntax: SELECT QUARTER("2018/07/18");
Output: 3
10.26 SECOND(): It returns the second portion of a date value.
Syntax: SELECT SECOND("09:14:00:00032");
Output: 0
10.27 SEC_TO_TIME(): It converts numeric seconds into a time value.
Syntax: SELECT SEC_TO_TIME(1);
Output: 00:00:01
10.28 SYSDATE(): It returns the current date and time.
Syntax: SELECT SYSDATE();
Output: 2018-07-18 09:19:03
10.29 TIME(): It extracts the time value from a time/date time expression.
Syntax: SELECT TIME("09:16:10");
Output: 09:16:10
10.30 TIME_FORMAT(): It formats the time as specified by a format mask.
Syntax: SELECT TIME_FORMAT("09:16:10", "%H %I %S");
Output: 09 09 10
10.31 TIMEDIFF(): It returns the difference between two time/datetime values.
Syntax: SELECT TIMEDIFF("09:16:10", "09:16:04");
Output: 00:00:06
10.32 WEEK(): It returns the week portion of a date value.
Syntax: SELECT WEEK("2018-07-18");
Output: 28
10.33 WEEKDAY(): It returns the weekday index for a date value.
Syntax: SELECT WEEKDAY("2018-07-18");
Output: 2
10.34 WEEKOFYEAR(): It returns the week of the year for a date value.
Syntax: SELECT WEEKOFYEAR("2018-07-18");
Output: 29
10.35 YEAR(): It returns the year portion of a date value.
Syntax: SELECT YEAR("2018-07-18");
Output: 2018
10.36 YEARWEEK(): It returns the year and week for a date value.
Syntax: SELECT YEARWEEK("2018-07-18");
Output: 201828
.png&description=SQL Tutorials - 4){kind=link}
0 Comments