Skip to main content

SQL Basics: Basic Select Statement -- Projection

Labels:

Comments

vijjubs said…
Hey Mithun,

The slides are not in sync with the presentation,
plz check and rectify.

vijjubs@gmail.com
March 31, 2011 at 11:12 PM
hema said…
what is the difference between sql and sql*plus
July 31, 2011 at 6:30 PM
hema said…
what is the difference between sql and sql*plus
July 31, 2011 at 6:30 PM
hema said…
string='laptop'.i want to print in reverse order.how to write a query
July 31, 2011 at 6:32 PM
Sundar said…
Hi Sir,

For few tables in online oracle database, SELECT statement is not working or not giving the output. But, describe table_name; is working for all table.

I am unable / dont know to post the screenshot here. If possible, pls check in online database for table named "EMPLOYEE"

Any how i've copied & pasted my SQL execution below. Pls have a look:

describe employee;

Results Explain Describe Saved SQL History
Object Type TABLE Object EMPLOYEE
Table Column Data Type Length Precision Scale Primary Key Nullable Default Comment
EMPLOYEE EMPID NUMBER - 5 10 1 - - -
EMPNAME CHAR 50 - - - - - -
EMPPHNO NUMBER - 10 20 - nullable - -
EMPADDR VARCHAR2 100 - - - - - -
EMPEMAIL VARCHAR2 30 - - - nullable - -
1 - 5
Save SQL

Value RequiredName
Description
Cancel Save


& pls see this too:

select * from employee;

Results
Explain
Describe
Saved SQL
History


no data found


PLEASE EXPLAIN REGARDING THIS!

Thanks & Regards,
Sundar
August 4, 2011 at 1:17 PM
Sundar said…
Hi Sir,

Please say whether the following sql statements are true or false & also say why if it is false...

1. SELECT * From *;
(Here, tablename is *)

2. SELECT * From linesize;
(Here, tablename is linesize)

3. SELECT * From SELECTFROM;
(Here, tablename is SELECTFROM)

Thanks & Regards,
Sundar
August 6, 2011 at 12:20 PM
Aurobinda said…
hello sir,
i hv installed oracle 10g.and i can't do the edit option and as well as how to spool.
August 10, 2011 at 1:55 PM
Mithun Ashok said…
1. SELECT * From *;
(Here, tablename is *)

False, you cannot have a table by name *.

2. SELECT * From linesize;
(Here, tablename is linesize)

True, linesize is a sql*plus keyword not sql keyword.

3. SELECT * From SELECTFROM;
(Here, tablename is SELECTFROM)

True, as there is key word in SQL as SELECTFROM.
August 12, 2011 at 6:14 AM
Mithun Ashok said…
Hi Aurobinda,

This could be because you do not have write permissions on the default path. Use the following command to set the buffer file.

SET EDITFILE 'location and filename'

This location and file should be the path where you can create files on your OS.

To spool use,

SPOOL 'location and filename'


This location and file should be the path where you can create files on your OS.
August 12, 2011 at 6:16 AM
Mithun Ashok said…
Sundar,

I hope you are able to query table online and your issue is resolved.

Regards,
Mithun
August 12, 2011 at 6:17 AM
Sundar said…
Hi Sir,

1. select * from selectfrom
(Here, 'select' is a keyword & 'from' is a keyword, whereas "'selectfrom'(no space in between 'select' and 'from')" is not a keyword right?
U've said that the statement is true, but y u said 'selectfrom' is a keyword? If it is so, then the statement should be false na?

2. Online database works only partially. Sometimes giving result & sometimes not giving the required output (even for select * from table_name). So, i've stopped writing queries in online database & working with the downloaded one which is very fine!

Had been waiting for ur answers for a long time & finally thanks for answering to all of them.

Thanks & Regards,
Sundar
August 12, 2011 at 12:44 PM
Mithun Ashok said…
Hi Sundar,

1. That was a typo, I meant there is no keyword as selectfrom.

select * from selectfrom; will work.

2. SQL*Plus commands will not work on online database. About giving output sometimes and not giving output sometimes, I guess there is some network problem as Online database works absolutely fine, there are many projects and online applications which have been developed using this application and it uses the same database that you have installed on your machine. If you have noticed something that is not working then let me know I can help you solve the problem.

Regards,
Mithun
August 12, 2011 at 5:52 PM
RAHUL AGARWAL said…
Sir,please explain interpretation of the following question:
"display details of all employee in the following format.
ename as scott,job as manager,sal>1000.(today's test 20th question)

Regards,Rahul.
September 5, 2011 at 11:31 PM
Mithun Ashok said…
Hi Rahul,

Ans for the question is,

select ename || ' is a ' || job || ' getting salary ' || sal from emp;

Regards,
Mithun
September 10, 2011 at 7:48 AM
Rituraj kashyap said…
hi sir
I want to revise SQL Classes again coz I missed few classes previously.So Will you tell me when you r going to start new batch of your's coz I am interested in your's class only..
January 16, 2012 at 12:35 AM
Rituraj kashyap said…
hi sir
I want to revise SQL Classes again coz I missed few classes previously.So Will you tell me when you r going to start new batch of your's coz I am interested in your's class only..
January 16, 2012 at 12:40 AM
Nyamleezekiel said…
what is the difference in functions of PROJECT and SELECT statements in DBMS
January 26, 2012 at 11:51 PM
Nyamlee said…
what is the difference in functions of PROJECT and SELECT statements in DBMS
January 26, 2012 at 11:53 PM
A G Ravindran said…
sir how to download this basic sql slides sir
March 6, 2012 at 1:44 AM
swathi said…
hi sir how to download this basic sql.
July 13, 2012 at 2:49 PM
Sarveshshettar08 said…
http://download.cnet.com/Oracle-Database-10g-Express-Edition/3001-10254_4-75220235.html?spi=4e88c24618975a538a1313663c924169&dlm=0


sir this link downloads oracle database 10g express edition. But i haven't tried yet. Because im not so sure that this is the same tool which u r using in the class. And also my internet speed is dam slow.
July 13, 2012 at 8:58 PM
Mithun Ashok said…
Hi Sarvesh,

Do not download 10g Express edition.

Mithun
July 14, 2012 at 7:46 AM
Mithun Ashok said…
Ravindran,

These slides are not for download. Its only for reference.
July 14, 2012 at 7:47 AM
Mithun Ashok said…
Hey Nyamlee,

Projection is only selecting columns. But SELECT is a clause used to both projection and selection (selecting rows)
July 14, 2012 at 7:48 AM
Sarveshshettar08 said…
k sir, anyways im practising online which u had asked us to create sir..... it's good to practise. & thanx for the reply sir.....:)
July 14, 2012 at 10:51 PM
Sindhu k.m said…
HI SIR,
this is sindhu
I could not create file but while
executing statement it shows file created but while editing the file will not open .
August 29, 2013 at 7:03 PM
Post a Comment

Popular posts from this blog

SQL Interview Questions on Subqueries

SUB Queries:
1. List the employees working in research department 2. List employees who are located in New York and Chicago
3. Display the department name in which ANALYSTS are working
4. Display employees who are reporting to JONES
5. Display all the employees who are reporting to Jones Manager
6. Display all the managers in SALES and ACCOUNTING department
7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them
8. Display all employees who do not have any reportees
9. List employees who are having at least 2 reporting
10. List the department names which are having more than 5 employees
11. List department name having at-least 3 salesman
12. List employees from research and accounting having at-least 2 reporting
13. Display second max salary
14. Display 4th max salary
15. Display 5th max salary  -- Answer for nth Max Salary
Co-Related Subqueries:
16. Write a query to get 4th max salary from EMP table
17. Write a query to get 2nd…
21 comments
Read more

'Linux-x86_64 Error: 28: No space left on device' While trying to start the database -- Error

SQL> startup mount pfile='/tmp/initdlfasp12.ora'
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device


This as you can see is on Linux x86 with 64 bit processor. We got this error after we changed SGA on 10gR2 database. So was sure that this is something to do with the OS.

Parameters to check for this are shmall.

shmall is the total amount of shared memory, in pages, that the system can use at one time.

$cat /proc/sys/kernel/shmmax
53687091200

$ getconf PAGE_SIZE
4096

As per Oracle SHMALL should be set to the total amount of physical RAM divided by page size.

Our system has 64GB memory, so change kernel.shmall = 1024 * 1024 * 1024 * 64 / 4096 = 16777216


Once this value is calculated you can modify Linux system configuration file directly.

$ su - root
vi /etc/sysctl.conf file:
kernel.shmall=16777216

and

# sysctl -p

Once this is done the database was started without any problem.
13 comments
Read more

Answers for SUB Queries

1. SQL> select empno, ename from emp where deptno=(select deptno from dept where dname='RESEARCH');


2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));

3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST');

4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES');

5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES')

6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING'))

7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp)

8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not null)

9. select…
8 comments
Read more