Data Small set of information becomes data, this set of information helps make decision. Data is always some useful information. Database Place where you store the data. Database represents some aspect of the real world called "miniworld". A database is designed, built and populated with data for a specific purpose. It has intended group of users and some preconceived applications in which these users are interested. In other words, a database has some source from which data is derived, some degree of interaction with events in the real world and an audience that is actively interested in the contents of the database. Database can also be defined as collection of one or more tables. Ex: Mobile, human brain etc DBMS (Database Management System ) Is a program that stores retrieves and modifies data in the database on request. Study of different techniques of design, development and maintenance of the database Types of DBMS These types are based upon their m...
Comments
1) create table products(
proid number(4),
pname varchar(10) not null,
qty number(4));
while creating the table i forgot
to proid as primary key & qty as check constraint.how to add these constraints to these columns
2)are foreign key & reference key one & the same ?
1) create table products(
proid number(4),
pname varchar(10) not null,
qty number(4));
while creating the table i forgot
to declare proid as primary key & qty as check constraint.how to add these constraints to these columns
1. Any constraint can be added or dropped at any point in time.
To add the constaints,
ALTER TABLE products ADD PRIMARY KEY(proid);
ALTER TABLE products ADD CONSTRAINT checkqty CHECK(qty > 1);
To drop use,
ALTER TABLE products DROP PRIMARY KEY;
ALTER TABLE products drop CONSTRAINT checkqty;
2. No Foreign Key and Reference Key are not same. Though none of the authors/books on DBMS define reference key(as far as my knowledge is concerned), I differentiate foreign and reference key as,
A foreign key is a referential integrity constraint that creates relationship between tables. Foreign is one of the constaints.
A Reference key is the primary key of table referred by a table(could be same table or another table) . Reference key is just a name given and not a constraint.
Look at the example below,
CREATE TABLE emp1(empno number(4) primary key,ename varchar(10) not null,age number(10) check (age >14), edeptno number(2) references dept(deptno));
Here emp.edeptno is a foreign key and dept.deptno is a reference key which is dept primary key.
i have a problm with 'edit'. its not executing and giving an error like "SP2-0110: Cannot create save file "afiedt.buf"
What can i do for that. Please help me out.
thank you.
In your Start --> All Programs Right Click on sqlplus icon and then click on Properties.
Under Shortcut tab, change "Start In" directory to either "C:\" or to "D:\" or any custom directory where you can store files.
Save/Apply the settings. Now login to fresh sqlplus from Start --> All Programs and try to use edit command.
Let me know if this helps.
Regards,
Mithun
No, its not working, when i change the drive i mena path and click on apply it gave me an error pop up window. that "the folder specified satrt inbox is not valid".
thanx. its working now.
Please discuss all SQL questions in your blog on the final day of our class! (If possible at your comfortness)
Also please discuss what type of difficulties we'll face while writing a sql query & how to overcome it!
Thanks & Regards,
Sundar
You can refer to,
Interview Questions
Regards,
Mithun
select * from emp
where sal > (select avg(sal) from emp);this ans is using subqueries...how to write using joins
2)display the dept names whose total salary(job wise) is more than 4500.pls give me solution using joins.
select b.dname
from emp a join dept b
on a.detno=b.deptno
group by b.dname
having sum(sal)> 4500...i think this query is wrong....pls send me correct ans.
already i posted these 2 qtns on aug 4th...but till now i didnt get reply....
1. Using join,
SELECT e.empno, e.ename,e.sal, e.deptno, b.avgsal
FROM emp e
JOIN
(SELECT ROUND(AVG(sal)) AS avgsal
FROM emp
) b ON e.sal > b.avgsal;
2. select b.dname,a.job, sum(sal)
from emp a join dept b
on a.deptno=b.deptno
group by b.dname,a.job
having sum(sal)> 4500;
Regards,
Mithun
thanks for sending ans...
can i use distinct with conditional operator or where clause???
select distict ename,sal from emp where sal=3000;
select distinct ename,sal from emp where sal< 3000;
i am still getting more than 2 values in my result set
Yes you can you distinct with where conditions.
When you use distinct like below,
select distinct ename,sal from emp where sal=3000;
ENAME SAL
---------- ----------
SCOTT 3000
FORD 3000
Here if you notice, SCOTT3000 and FORD3000 are distinct values together.
Regards,
Mithun
can i get it from your web site
You can refer to these documents and notes but you will not be able to download it. All the notes here are only for reference not for download.
Regards,
Mithun
"http://technologizer.com/2011/10/24/windows-xp/"
"http://arstechnica.com/microsoft/news/2011/10/ten-years-of-windows-xp-how-longevity-became-a-curse.ars"
i want to know how index work.
i mean how it is useful to access database in faster way.
i want to know how index work in retrieving the data in faster way.
i executed the statment
CREATE INDEX salary ON emp (sal);
but i don't know how to use it.
HOW TO DISPLAY THE NTH ROW
How to delete duplicate rows from a table using DELETE
While I try to create a new user by following statement:
create user QP identified by xx;
It comes as 'insufficient privileges'
Please let me know why?
please send me 3+ years exp cv's to my mail:
sreenivasamoorthy67@gmail.com
You need to prepare your resume by yourself. I can only tell you how to prepare your resume and I do not have any resumes with me.
how can i find second highest salary in sql without using max function.
Write a query to get 2nd & 6th max salary from EMP table
whats exactly happen in this two query
select 'abc'from emp;
select 'abc'abc from emp;
why in the first query in column heading c is not coming.u can xplain in evening 4 o clock jspider batch.i am from dere
whats exactly happen in this two query
select 'abc'from emp;
select 'abc'abc from emp;
why in the first query in column heading c is not coming.u can xplain in evening 4 o clock jspider batch.i am from dere
whats exactly happen in this two query
select 'abc'from emp;
select 'abc'abc from emp;
why in the first query in column heading c is not coming.u can xplain in evening 4 o clock jspider batch.i am from dere
CREATE TABLE POTENTIAL(START DATE DD-MM-YY,CLOSED DATE DD-MM-YY)
SELECT * FROM EMP
WHERE SUBSTR(JOB,1,3)='MAN';
--will select all the employees who are mangers
SELECT * FROM EMP
WHERE SUBSTR(JOB,1)='MAN';
--says no rows selected
why is it so?
Can u include questions on joins as well as rownum & rowid?
Also i have doubt that why SELECT* from emp where rownum=2; gives no rows selected and select* from emp where rownum<=2 returns two rows from emp table.
Thanks
display the columns which has no null values
Soln
Select emp.*,ename from emp;
How to write a query to display
1st row- Max sal
2nd row -main sal
3rd row- 2nd Max sal
4th row - 2nd main sal
SQL Interview Questions & Answers.
Data Science Training in Pune