Database basics

When you want data based on two tables, it doesn't mean that you have to perform JOIN always. Below query gets the details(Id,Name,Description) of product, which are not sold event once. Approach: Try to convert this English in to logic of columns, like all product.id, which are not present in sales.productId Generally sub-query/inner query results are fed in to outer queries.

Where and Having both are used to apply filter Difference: You can not use Aggregate function in where clause, like select * from employee where SUM(salary) > 5000; but you can use Aggregate function in having clause. But having has to be used in conjunction with " GROUPED BY " clause. GROUP BY, makes the list of group, say if you have applied two " Grouped by" select city,gender   from employee   grouped by city, gender; in above case, you will have below type of  data Group-1 id  name      city      gender salary 2   Dharmu  BLR    M         4000 8   deepak    BLR   ML       5000 Group-2 id  name      city      gender salary 1   rashmi    BLR    F          10000 3   Sargun    BLR...

The output should be lie this from an EMPLOYEE table Employee Emp # Manager Mgr # BLAKE 7698 KING 7839 CLARK 7782 KING 7839 JONES 7566 KING 7839 MARTIN 7654 BLAKE 7698 ALLEN 7499 BLAKE 7698 TURNER 7844 BLAKE 7698 JAMES 7900 BLAKE 7698 WARD 7521 BLAKE 7698 FORD 7902 JONES 7566 SMITH 7369 FORD 7902 SCOTT 7788 JONES 7566 ADAMS 7876 SCOTT 7788 MILLER 7934 CLARK 7782 TRY SELF JOIN, with two alias of same table, considering then as separate two tables SELECT e . ename , e . empno , m . ename as manager , e . mgr FROM emp e , emp m WHERE e . mgr = m . empno

Oracle schema = Oracle user + database objects owned by that user. When an Oracle user is created using the CREATE USER command, a schema gets created for the user by default with the same name as User There is a one-to-one correspondence between Oracle user name and Oracle schema name. While user = schema in most circumstances, that isn’t true all the time. CREATE SCHEMA does not actually create a schema. It only lets you create multiple tables/views and grant privileges on them in your own schema, in a single transaction. Example:  Here’s an example of using CREATE SCHEMA in the schema “hr” to create a table, create a view on that table, and give grant to another user on that view. SQL> CREATE SCHEMA AUTHORIZATION hr

If two shorted city like AMO,DIO, then display the one which come first in alphabetic order, here AMO Not the best, union can also we used. select  *  from (select city,CHAR_LENGTH(city) as citylen from station order by citylen,city ASC) T1 limit 1 ; select  *  from (select city,CHAR_LENGTH(city) as citylen from station order by citylen DESC) T1 limit 1;  

Group by <SameColumn>, this basically  group the rows having same column value. lets say, --> Group by department, this this will club all the rows which has same department, and it will have some sort of list of list, 1st position, list of 6 rows, from CSE department 2nd row,      list of 2 rows from ECE department Now in these rows, you can only perform some aggregate funtion and display it, you can display employee name of department as output, because one collection, say 1st position, may have 6 rows each having different employee name, what it will display? i.e. you should/can perfrom only agregate function on this grouped by list. select count(CustomerID) as countryhead,country,city from customers group by Country,city order by countryhead DESC; countryhead Country City 6 UK London 5 Mexico México D.F. 4 Brazil São Paulo 3 Argentina Buenos Aires 3 Brazil Rio de Janeiro 3 Spain Madrid