1. What is Inner Join? Please explain with examples.
2. What is Outer Join? Please explain with examples.
3. Please write complete SQL query using the following:
where, insert, update, delete, order by, in, between, inner join, left join, right join, union, drop, alter, group by.
Subscribe to:
Post Comments (Atom)
4 comments:
Question # 1: What is the most complex SQL Queries you have written?
In my previous project I had written the SQL quary from the different tables of database by using alias .
Question # 2: Retrieve all the data from Class?
SELECT * FROM CLASS ;
Question # 3: Retrieve all the data from QAClassFall06?
SQL< Select * from Tab; (enter)
SQL< Select * from QAClassFall06; (enter)
Question # 4: Display Class name and Class location from Class?
SELECT CLASS _NAME ,CLASS_ LOCATION FROM TABLE ‘ CLASS
Question # 5: Display Last name, First name, ID and Cell phone from QAClassFall06?
;
Question # 6: Insert four rows of data in Class?
SQL< insert into Class values
(6, ‘QA’, ‘Virginia’, ‘Chantilly’);
Question # 7: Insert one row of data in QAClassFall06?
SQL< insert into QAClassFall06 values
(6);
Question # 8: Write the Syntax for the following:
Drop, Delete, Truncate
SQL< drop table Class;
SQL< delete Fname from Class;
SQL< truncate from Class;
Question # 9: Join 3 tables using alias?
Tables Datas
Finance / Fname, Laname, ID No., SSN, Salary
/
Personnel Dept / Position, SSN, Division, Dept no.
/
Procurement / Fname, Lname, SSN, Section
---------------------------------------------------------------------------------------------------
Ans: Here, I have given alias as A, B and C for the tables respectively.
SQL< Select A. Fname, B. Position, C. Lname
Where
A. Fname = C. Fname
And
A. SSN = B. SSN = C. SSN ( this is my personal syntax)
Murari Naral
1. What is an Inner Join?
Inner joins are exclusive joins. They match records together based on one or more common columns and return only the matched rows. An inner join:
- uses a comparison operators.
- Returns a result that contains only joined rows that satisfies the join condition(s).
- Is the most common type of join.
To create an Inner join: (Syntax)
SELECT columns
FROM table1
INNER JOIN table2
ON join_conditions
We have 2 tables with the following columns.
Table1: emp columns: emp_id, dept_id, fname, lname, ssn, dob
Table2: dept columns: dept_id, dept_name, mgr
SELECT e.fname, e.lname, d.dept_name, d.mgr
FROM emp e
INNER JOIN dept d
ON e.dept_id = d.dept_id;
The same query can be written as:
SELECT e.fname, e.lname, d.dept_name, d.mgr
FROM emp e, dept d
WHERE
e.dept_id = d.dept_id;
Result will be:
Fname lname dept_name mgr
Caison Mary QA Johnny Smith
Wilson Harry Devt. Mark White
2. What is an Outer Join? Explain
Unlike the standard join which returns only corresponding rows in each of the joined tables or views, an outer join allows rows to be returned even if no matching row exists in one of the tables. The outer join is specified by key words ‘LEFT [OUTER] JOIN’ or ‘RIGHT [OUTER] JOIN or FULL [OUTER] JOIN. Some database including ORACLE accept the plus sign + to be added to the end of the column names for the table that is being taken as optional.Outer joins are inclusive in nature. Examples RIGHT, LEFT and FULL JOIN respectively:
SELECT ename, job, d.deptno, dname
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
ENAME JOB DEPTNO DNAME
---------- --------- ---------- -----------------------------------------
CLARK MANAGER 10 ACCOUNTING
KING PRESIDENT 10 ACCOUNTING
SCOTT ANALYST 20 RESEARCH
JONES MANAGER 20 RESEARCH
JAMES CLERK 30 SALES
TURNER SALESMAN 30 SALES
40 OPERATIONS
88 DEVELOPMENT
99 TESTING
SELECT ename, job, d.deptno, dname
FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;
ENAME JOB DEPTNO DNAME
---------- --------- ---------- -----------------------------------------
CLARK MANAGER 10 ACCOUNTING
KING PRESIDENT 10 ACCOUNTING
SCOTT ANALYST 20 RESEARCH
JONES MANAGER 20 RESEARCH
JAMES CLERK 30 SALES
TURNER SALESMAN 30 SALES
The same syntax can be written as:
SELECT ename, job, d.deptno, dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno; (or e.deptno = d.deptno(+) for LEFT JOIN)
SELECT ename, job, d.deptno, dname
FROM emp e
FULL OUTER JOIN dept d
ON e.deptno = d.deptno;
Note: The word ‘OUTER’ is optional.
Complete SQL Query using the following:
Where, insert, update, delete, order by, in, between, inner join, left join, right join,
Union, drop, alter, group by
UPDATE table emp
Set deptno = 99
Where empname = ‘JOHN’;
INSERT INTO emp
VALUES(10,’KARAN’, ‘JOHOR’, ’01-DEC-1998’,10);
DELETE from EMP
WHERE EMPID = 123;
SELECT * from emp
ORDER BY deptid;
SELECT * from emp
WHERE deptno IN 10;
SELECT * from emp
WHERE hiredate BETWEEN ’01-JAN-81’ and ’01-JAN-82’;
SELECT * from emp e, dept d
Where e.deptno = d.deptno;
SELECT e.*, d.* FROM emp e, dept d
WHERE
e.deptno(+) = d.deptno;
SELECT e.*, d.* FROM emp e, dept d
WHERE
e.deptno = d.deptno(+);
SELECT deptno from emp
UNION
SELECT deptno from emp;
(UNION keyword works like DISTINCT)
ALTER table emp
DROP column comm.;
SELECT deptno, count(*) from emp
GROUP BY deptno;
Yuba R Dhakal 01/31/2007
1. What is inner join? Explain.
# An inner join is a join that selects only those records from both database tables that have matching values in both the table. To create an Inner join: (Syntax)
SELECT columns
FROM table1
INNER JOIN table2
ON join_conditions
2. What is outer join? Explain.
# An outer join selects all the records from one database table and from the other table it takes only matching values. In a left outer join, all the selected records will include from the first database table. In a right outer join, all the selected records will include from the second database table.
3.
UPDATE table class set Lname = ‘Sharma’ where deptno = 3;
INSERT INTO class
VALUES(101,’Yuba’, ‘Dhakal’, 7035890785);
DELETE from class
WHERE classid = 3;
SELECT * from class
WHERE classid IN 4;
SELECT * from class
WHERE classid BETWEEN 3 and 6;
Alter table class
Drop column Lname;
From: - Subash Singh
What is inner joins?
Inner joins combines data from two or more columns from multiple tables when both the tables have matching rows of data. In contrast, returns all the rows of tables where there is match.
E.g. SELECT A.CITYNAME B.COUNTRYNAME B.CONTINENT
FROM TABLE A, TABLE B
WHERE A.CODE=B.COUNTRYCODE AND A.CODE=”NEPAL”
What is outer joins?
Combines data from multiple tables to show records from both tables, and fill in NULLs for missing matches on either side.
E.g. SELECT DISTINCT *
FROM EMPLOYEE
OUTER JOIN DEPARTMENT
ON EMPLOYEE.DEPARTMENTID = DEPARTMENT.DEPARTMENTID
3. Please write complete SQL query using the following:
where, insert, update, delete, order by, in, between, inner join, left join, right join, union, drop, alter, group by.
WHERE:-
SELECT * FROM COUNTRY WHERE COUNTRYNAME=”NEPAL” OR COUNTRYNAME=”INDIA”
INSERT:-
INSERT INTO COUNTRY (COUNTRYNAME, POPULATION) VALUES (“JAPAN”, 3500000)
UPDATE
UPDATE COUNTRY SET POPULATION=37000000 WHERE COUNTRYNAME=”JAPAN”
DELETE:-
DELETE FROM COUNTRY WHERE COUNTRYNAME LIKE “%J”
DRDER BY:-
SELECT COUNTRYNAME, CONTINENT, POPULATION FROM COUNTRY ORDER BY COUNTRYNAME DESC
IN:-
SELECT * FROM COUNTRYNAME WHERE COUNTRYNAME IN (‘NEPAL’,’INIDIA’,’CHINA’)
BETWEEN:-
DELETE FROM COUNTRY WHERE POPULATION BETWEEN 20000 AND 50000
INNERJOIN:-
SELECT * FROM COUNTRY
INNER JOIN CITY
ON COUNTRY.COUNTRYCODE= CITY.CODE
LEFTJOIN:-
SELECT *
FROM COUNTRY
LEFT OUTER JOIN CITY
ON COUNTRY.COUNTRYCODE = CITY.CODE
UNION:-
SELECT E_NAME FROM EMPLOYEES_NORWAY
UNION ALL
SELECT E_NAME FROM EMPLOYEES_USA
DROP:-
ALTER TABLE COUNTRY DROP STATES
ALTER:-
ALTER TABLE COUNTRY ADD REGION
GROUP BY:-
SELECT * FROM COUNTRY GROUP BY CONTINENT
Post a Comment