-
Query to Get Highest Marks Output
Question: student marks stable "sp_report"
sid maths chem phy engl
1 33 45 54 48
2 55 32 50 37
3 10 20 30 40
The required o/p is (SQL QUERY TO GET THE BELOW O/P)
sid highest_marks
1 54
2 55
3 40
Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL)
What is table space?
I want to know the answer
amitarudra
- Aug 7th, 2009
Every database is divided into logical storage unit? called Tablespace. This Tablespace is used to group related logical strutures together.
pujavipin
- Jun 9th, 2009
Table space is the logical storage area on which different users would have quota to create and maintain their objects and data.
How to display duplicate rows in a table?
Select * from emp where deptno=any(select deptno from emp having count(deptno)>1 group by deptno);
ashwani7jul
- Jan 20th, 2010
DELETE FROM test USING test, test AS vtable WHERE (test.ID > vtable.ID) AND (test.name=vtable.name)Deleted duplicate records query
mayank1710
- Mar 26th, 2009
Select * from acctcycle where acct_nbr in (select acct_nbr from acctcycle group by acct_nbr having count(acct_nbr)>1);
What is Dual table?
What is dual table which is used as a table in sql...
Read Best Answer
Editorial / Best Answer
svsraju81- Member Since Jul-2008 | Jul 24th, 2008
Dual table is a temporary table, it contains a single row and single column. If we return a data from dual table it gives a single row.
sudipta kumar dhal
- Sep 5th, 2011
Dual table is called a dummy table because it is not meant for storing any useful data.
d_pranjal
- Sep 5th, 2010
Dual table is owned by SYS. Data dictionary come under SYS, dual is part of data dictionary. Dual table consist only one row and only one column containing value x
HAVING Clause
Given a SELECT statement that has a GROUP BY clause.The HAVING clause uses the same syntax as which other clause?A. WHEREB. UNIONC. SUBQUERYD. ORDER BY
Ksudhir_Kumar
- Sep 5th, 2010
Having clause is used filter data which is retrieve by group by clause.for example: if you try to use having to filter data instead of where clause then it fires error.the style of query is:- 1...
What is the result of the fallowing code?PieceInsert into table A value(a1):CREATE TABLE B AS SELECT * FROM A;ROLLBACK;A. Table B gets created with the row inserted in the first statement.B. Table B is not createdC. Table B gets created, but no row gets inserted into Table AD. Rollback throws up an exception.
Guest
- Jan 4th, 2007
hian empty table table (B) will be created there is a mistake un the first statementno need to commit a Create instruction, so the rollback will have no effectgood luck
What is difference between Oracle and MS Access? What are disadvantages in Oracle and MS Access? What are features&advantages in Oracle and MS Access?
Jee Va
- Sep 17th, 2011
Hi! everyone, I've to develop a small retail store management application for our Store. I've selected the front end as JAVA and back end is ??? (Still confusing) to use 1.Using File Handling(but i do...
ywaikar
- Jul 17th, 2009
Both Oracle and MS Access are RDBMS, Oracle followed Client-Server architecture while MS Access is does not.We can have Login Security, command level security in Oracle.Can define relationship at time of table creation.
SQL - Find top two salary for each department
1. top 2 salary in each dept2. 2nd maximum sal in each dept3. 2nd minimum sal in each dept.
Prasanth
- Sep 12th, 2022
SELECT d.department_id, d.salary, d.employee_id FROM
(SELECT employee_id, department_id, salary, dense_rank() over(partition by department_id order by salary desc ) as salary_DenseRank from employees) d
WHERE d.salary_DenseRank =2;ashadevi21
- Sep 27th, 2011
The Same can be modified and can be used for 2nd maximum and 2nd minimum as well For 2nd Maximum =========== SELECT dept_id, salary FROM (SELECT dept_id, salary ...
Command to show SQL commands from buffer
Which command displays the SQL command in the SQL buffer, and then executes it?
sandeep.katta
- Feb 3rd, 2011
RUN Command...... Check by following exampleSQL> select sysdate from dual;SYSDATE---------03-FEB-11SQL> run 1* select sysdate from dualSYSDATE---------03-FEB-11
hemant.agarwal03
- Mar 17th, 2008
LIST and L is correct but you can also type "ED" also on sqlplus window and enter, it will open a notepad window for you to edit (if you required) otherwise you can press ALT + F4 and ENTER key.
TRUNCATE TABLE EMP;,DELETE FROM EMP;, Will the outputs of the above two commands?
Both will result in deleting all the rows in the table EMP.Answered by Scott on 2005-05-12 10:31:58: The difference is that the TRUNCATE call cannot be rolled back and all memory space for that table is released back to the server. TRUNCATE is much faster than DELETE and in both cases only the table data is removed, not the table structure.
yuvaevergreen
- Jun 14th, 2011
In Teradata,Drop table and delete table are the options. delete table is preferred.
vinod.km
- May 14th, 2009
CREATE TABLE ABC
(
Col1 INT
)INSERT INTO ABC VALUES (1)
BEGIN TRAN
TRUNCATE TABLE ABC
ROLLBACK TRAN
SELECT * FROM ABCIn this way, I am able to roll back truncate command. So in
SQL server we can Roll Back TRUNCATE Command.What operator performs pattern matching?
saurabh
- Sep 5th, 2011
Underscore operator (_) for single character matching n wildcard (%) for any length string matching...
example a column having abhi,abhaaa, bhaaa,bmddd,cmmm;and i want all those name that start with a then use like 'a%';Deleting duplicate records in a table without using rowid
1. How to delete a duplicate records in a table without using rowid?
2. What is the use of Connect by clause?
3. What is the use of Connect by clause?
4. How to display "Experience of employee"?E.g. 3 years 4 months 19 days?
5. What is select statement to spe?Muralidhar
- Apr 6th, 2012
We delete the duplicate records using level and rownum. The query is
Codepravesh28
- Nov 28th, 2010
1 Delete from table_name where ROWID not in ( selectmax(rowid) from table group by duplicate_values_field_name);
What de-normalization and when do you do it?
sweetytrivedi
- Jun 18th, 2008
Denormalization is the process of attempting to optimize the performance of a DB by adding redundant data or by grouping data. The recommendation is always to use this for teh logical design
hari prasad
- Mar 19th, 2007
When its a large data base and our main constraint is the cost(time) than we concentrates on fast retriving .so we go for denormalization instade of joing(complex relation calculcus) too many table and be in much complexicity with hign normalization.
How many trigger we can create on a table? How many column we can create in a table?
Janmejay Rout
- Jul 13th, 2015
Hi,
You can create 12 triggers on a table.
indira_priyadarsini
- Jul 29th, 2009
Only 3 triggers can be created on a table. i.e (Insert,Update & Delete)and Maximum of 1024 columns can be created in a table.
How do you delete duplicate row from oracle table that does not have any primary key field?
raji_4u
- Sep 19th, 2008
Consider a table has two columns and no primary keyemp---------------------empno ename---------------------1 AA2 BB3 CC1 DD --> not duplicate3 ...
Lavanya Chowdary
- May 6th, 2007
We can use RowID if you are not having any primary key for column.
How to get the First name,second name,Third name ?
I have a column in a table with names.Now i want to select that names like first name,second and third.I/p like this----- names Chandu Kannaka prasad Rama krishna Kiran kumar Kora Balaji Chandra sekhar pottaO/p I want------ Firstname Secondname Thirdname ...
skcwebworld
- Aug 7th, 2015
"tsql declare @Name NVarchar(MAX), @FName NVarchar(MAX), @MName NVarchar(MAX), @LName NVarchar(MAX) select @Name = SAURABH KANT CHAKRAVARTI --select @Name = SAURABH KANT s...
farhanbaig
- Jun 7th, 2011
I am guessing you are looking for initcap function which are as follows:select initcap(first_name||' '||second_name||' '||third_name) from employees;
Recent row entered
I have a very unusual question, how can one check which is the recent row added to a table?
Read Best Answer
Editorial / Best Answer
biswaranjanbehera- Member Since Jul-2008 | Aug 6th, 2008
We can select the recent row enrtered by using the query below.
suppose the table name is employee;
then.
select * from employee where rowid=(select max(rowid) from employee).
This works fine because rowid is assigned for each and every record and in accending order. So the recent record's rowid is the man (rowid). Please send feedback with any concern.bulat
- May 31st, 2010
"Although a rowid uniquely identifies a row in a table, it might change its value if the underlying table is an index organized table or a partitioned table." Also rowid stores information a...
dachi316
- Aug 26th, 2009
SCOPE_IDENTITY() would be a better option to get the most recently added row as the primary key for a table may not necessarily be an auto-incrementing variable. Therefore max(rowid) would not be the optimal solution in that case.
Remove Primary Key
What happens if we remove primary key from SQL table?
pravesh28
- Mar 9th, 2010
No problem, we use rename operationand we relace the forigen key inti primary key
bikash_tamang
- Jul 10th, 2009
Once the primary key is removed two things will happen:1. The clustered index is also removed which decreases performance and2. The table would then allow duplicate data to be inserted causing data corruption.
Read Best Answer
Ans