SQLnPLSQLGuru

Most databases live and work in one time zone. If you have a database that covers more than one time zone, you might consider storing time in UTC and adding a numeric column to hold the local time-zone offset.The time zones start at UTC, which has an offset of zero. This is how the system-level time-zone table in Standard SQL is defined. Theory Behind Time There are also ISO-standard three-letter codes for the time zones of the world, such as EST, for Eastern Standard Time, in the United States. The offset is usually a positive or negative number of hours, but there were some odd zones that differed by 15 minutes from the expected pattern; these were removed in 1998. Now you have to factor in daylight saving time on top of that to get what is called “lawful time,” which is the basis for legal agreements. The U.S. government uses DST on federal lands inside states that do not use DST. If the hardware clock in the computer in which the database resides is the source of the ...

The below are some of the key points about nulls, and how to compare them. Just to make sure that you should know about what is NULL value. Interpretation is not exactly missing value. There could be many reasons why no value is present, e.g., value inappropriate. How to compare NULL to values In the below example the where condition just checks for TRUE condition. If not matches you will get UNKNOWN. Example: Table Name is: Sells bar beer price ======================= Joe's bar Bud NULL SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00; ------------ ------------- UNKNOWN Conclusion Joe's Bar is not produced, even though the WHERE condition is correctly given. The reason is the price value is NULL. So, we should not compare NULL to values.

What is SELECT in SQL query? When executed, a SELECT statement always returns a table. The table may have only one column and no rows, but every SELECT statement returns its query results as a table. Moreover, when a SELECT statement appears within another SELECT statement as a subquery, the inner SELECT statement returns a results table that serves as the input table for the outer (or main) SELECT statement. If you are using interactive SQL (such as the window-oriented MS-SQL Server Query Analyzer or the command-line MS-SQL Server ISQL application), the SELECT statement displays its query results in tabular form on your computer screen. If you are using a program (such as Visual Basic, C, or C++) to send a query (SELECT statement) to the DBMS, the DBMS will use a cursor to hold the tabular query results while it passes the rows of column values to your application program's (host) variables. SELECT with WHERE clause A SELECT statement with a WHERE clause will sti...

The default order of rows returned for a result table varies depending upon the specific nature of the query. However, generally you can assume that the order of result table rows is random. If a specific order is required then one must include the optional ORDER BY clause.                              SELECT [column(s)]                FROM [table(s)]                WHERE [row(s) to select]                GROUP BY [column(s) for summaries]                HAVING [groups to select]                ORDER BY (Cols to sort) The ORDER BY clause is almost always placed at the end of the SELECT statement (although some advanced statement structures beyond the scope of this course could alter this general rule). The...

You will use the below SQL to use to_char function correctly. select first_name, last_name, to_char(hire_date, 'Day, Month DDTH, YYYY') formatted_hire_date from hr.employees; Results from our TO_CHAR formatting appear with the correct day name, month name and other desired formatting in place. FIRST_NAME            LAST_NAME       FORMATTED_ HIRE_DATE -------------------   -----------     -------------------- Donald                OConnell       Monday,June 21ST, 1999 Douglas               Grant          Thursday,January   13TH, 2000 Jennifer              Whalen ...

SQL query to retrieve all junior girls SELECT * FROM Member m WHERE m.MemberType = 'Junior'       AND m.Gender = 'F'; Conclusion The above query useful to retrieve all junior girls.

With the outer join, some table reordering is possible and recommended for efficiency.  Normally, hierarchical structures are built top-down, but when subviews are used, right-sided nesting can cause the structure to be built bottom-up.  Top-down execution is more efficient than bottom-up execution because bottom-up execution can cause throwaways. Throwaways are rows that are retrieved into the working set and then later discarded. SQL Expanded (Bottom-Up): SELECT* FROM Department LEFT JOIN         Employee LEFT JOIN Dependent           ON EmpNo=DpndEmpNo            ON DeptNo=EmpDeptNo   SQL Rewritten (Top-Down): SELECT * FROM Department LEFT JOIN Employee ON DeptNo=EmpDeptNo LEFT JOIN Dependent ON EmpNo=DpndEmpNo

SELECT * FROM Member m ORDER BY m . LastName

LIST THE EMPS IN THE ASC ORDER OF DESIGNATIONS OF THOSE JOINED AFTER THE SECOND HALF OF 1981. SELECT * FROM EMP WHERE HIREDATE > ( ’ 30 - JUN - 81 ’ ) AND TO_CHAR ( HIREDATE , ’ YYYY ’ ) = 1981 ORDER BY JOB ASC ;

SELECT EMPNO , ENAME , JOB , HIREDATE , MONTHS_BETWEEN ( SYSDATE , HIREDATE ) EXP FROM EMP WHERE EMPNO IN ( SELECT MGR FROM EMP );

SQL query to list the employees who joined before 1981 SELECT * FROM EMP WHERE HIREDATE < ( ’ 01 - JAN - 81 ’ );

Natural Keys: -A natural key is a subset of attributes that occur in a table and act as a unique identifier. The user sees them. You can go to the external reality and verify them. Example: bar codes on consumer goods (read the package), geographic coordinates (get a GPS), or the VIN on an automobile (read the tag riveted to the dashboard, etched into the windows, and engraved on the engine block). Artificial Keys:- An artificial key is an extra attribute added to the table that is seen by the user. It does not exist in the external reality but can be verified for syntax or check digits inside itself. Example: the open codes in the UPC/EAN scheme that a user can assign to his own stuff. For example, a grocery store not only sells packaged good with preprinted bar codes but also bakes bread in the store and labels them with an open code. The check digits still work, but you have to verify them inside your own enterprise. Exposed Physical LOactors: An exposed physical loca...

The below SQL query we can get only Duplicates: SELECT NAME,EMAIL,COUNT(*) FROM DA31.TABLE1 GROUP BY NAME,EMAIL HAVING COUNT(*) > 1; So, after running this query we get only duplicate rows of NAME and EMAIL.