Find Highest Marks In Student Table Query
Oct 9, 2007
Hi Everybody
I've one table named Student. Here is data
Name
Subject
Mraks
Prasad
English
80
Tushar
English
79
Sunil
English
78
Prasad
Geometry
80
Tushar
Geometry
81
Sunil
Geometry
79
Prasad
History
82
Tushar
History
81
Sunil
History
80
Now I want to write a query that displays student with subjects and marks who secured highest marks in each subject.
I want to output like this
Name
Subject
Mraks
Prasad
English
80
Tushar
Geometry
81
Prasad
History
82
So will anybody help me to write a sql query that acheive the same output
View 5 Replies
ADVERTISEMENT
Jul 18, 2011
I have a requirement like, we are having two tables in our database.
Table names: student, marklist
Student table values:
id studname
------------------
1x
2y
3z
4a
5b
Marklist table values:
id maths physics English
---------------------------------
1506070
2706040
3508070
45010070
5906070
But my requirement is, I need to display the data "subject wise" highest marks for each student.
for example:
id name highestmark
---------------------------------
1 x English
View 9 Replies
View Related
Feb 12, 2013
I want to calculate average of grades of each student and get the highest one with SQL command.
I have 2 tables:
Students:
*StudentId
*StudentName
___________
Grades:
*StudentId
*Grade
___________
I need to calculate average of each student and then get the highest.
My try:
Code:
SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId
GROUP BY Students.StudentId, Students.StudentName
ORDER BY avg_grade
LIMIT 1 FROM Students;
I encounter problem with this code, maybe it's Completely wrong...
View 5 Replies
View Related
Aug 9, 2005
What is the query to find a 5th highest salary.in emp table.i also use top1,top2,..but i don't get a result.what is new in sql server 2005.
View 11 Replies
View Related
Aug 4, 2013
I have created a table:
CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;
And insert the following data:
INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)
How do i calculate the age and how can i group by the StudentNumber like 'IT%'
View 3 Replies
View Related
Nov 24, 2013
I have this table of Marks as shown below. All I need is to find the average Marks at various intervals of S.no. That is I need averages at every 3rd S.No. as shown.
S.No. Marks
1 ------ 5
2 ------ 5
3 ------ 6 1st Average Value here (16/3)
4 ------ 5
5 ------ 6
6 ------ 7 2nd Average Value here (18/3)
7 ------ 7
8 ------ 7
9 ------ 8 3rd Average Value here (22/3)
10 ----- 8
11 ----- 9
12 ----- 8 4th Average Value here (26/3)
So basically I need a new table which will have 4 average values for the table above. Of-course the table can be much bigger and the average values can be at any nth value of S.No.
View 12 Replies
View Related
Sep 15, 2006
I am stumped on a set-based approach for this one.
A cursor approach is straightforward enough, but i want to avoid that.
Here's my table:
create table StudentScores
(
id int primary key identity(1,1),
student_id int not null,
score int not null
)
with some sample data:
insert into StudentScores (student_id, score)
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 88 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 98
What I want is, for each student, what is their 90th percentile score?
For a given single student, one possibility would be:
declare @studentid int
set @studentid = 2
select top 1 @studentid as student_id, a.score as [90th percentile score]
from
(
select top 90 percent score from StudentScores
where student_id = @studentid order by score asc
) as a
order by a.score desc
But I want this for all students, and not use a cursor.
Any ideas?
Thanks!
View 6 Replies
View Related
Sep 26, 2004
hello all,
i'm not new to SQL but i cant seem to get this right:
hope some one will:
how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter)
i.e :
id | name | val
---------------
1 | name1 | 2.7
2 | name1 | 3.5
3 | name1 | 3.5
4 | name1 | 3.5
5 | name1 | 1.3
6 | name2 | 3.1
7 | name2 | 3.1
8 | name2 | 2.9
requested result:
if input param(name)=name1
result:
1 | name1 | 2.7
if input param(name)=name2
result:
8 | name2 | 2.9
hope some one can write the sql cmd for this
i'll be grateful !
thank you!
View 3 Replies
View Related
Sep 5, 2005
I'd like to know the current value of my uniqueID column before Icreate a new record.Is there a way to find out this value?It is numeric in my case, but I can't just look for the MAX value,since some records may have been deleted, and the value for theuniqueID still stays at the higher value.Is there a way to read this internally kept value?
View 5 Replies
View Related
Sep 20, 2015
I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.
@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal
-- First Cursor
DECLARE TESTCURSOR CURSOR
DYNAMIC
[Code] ....
The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.
SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id
View 9 Replies
View Related
Jul 10, 2014
I have a database of employees and pay rates.
One employee has two pay rates for two different jobs:
Job A: Rate $10.00
Job B: Rate $15.00
I will be updating their record so that they only have one job going forward, Job C. I need Job C to equal their HIGHER of the two existing jobs.
I have a select statement to find what the higher rate is. However, I am not sure how I can apply the rate to be the new job's rate. Here's what I used to find the highest rate for one single person:
SELECT max(rate), employeeID
FROM JobsTable
inner join IDTable
on JobsID2 = IDID2
WHERE JobCode in ('JOBA','JOBB')
and EmployeeID = '12345'
GROUP BY EmployeeID
(this returns the employee ID from one table, and the highest rate from Jobs A and B from another table)
I can get it to update to add JobC -- how can I get it to assign the result from the above query to be the rate used for Job C?
View 1 Replies
View Related
Jul 6, 2007
Hi,
My first post. I am trying to run a select query to a linked server (Cache database on VMS OS) from Microsoft SQL 2000 Query Analyzer.
Here is my problem query:
SELECT *
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM Member_Acct WHERE close_dt > {d'2007-07-01'}'
I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.
I have tried using double quotes and everythingelse I could. Database expect me to send date in this format {d'YYYY-MM-DD'}.
Please help me.
View 12 Replies
View Related
Oct 31, 2007
Hi There,
I'm having a little trouble with a large query I have written to be sent as an email.
I want to send it as a csv so I've added commas in between each value in the query.
This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ggsgn'
,@recipients = 'sgsdm'
,@subject = 'Nsgdday'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='
use sdgsgdgs
select
a1.c#,',', client,',', fr,',', tpe,',', dateReq as DateRequested,',', ... '
View 1 Replies
View Related
Feb 6, 2006
Hi folks, sorry for the poor explanation.
Im using SQL 2000
I have a database that has a column named 'Initials' in a char in field
I want to be able to return in a query the highest entries if an indiviuals initials & count from the table, so it would display some like this
Initials Count
DRT 51
AMS 49
JJJ 21
PLI 10
Hope u can help, thanks in advance
View 2 Replies
View Related
Aug 14, 2013
structuring a query to give me the CurrBal per CardNo.
I have table that looks like this:
CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-04 11:50 AM 79 0 6
2 2013-08-05 15:34 PM 52 100 41
2 2013-08-05 14:11 PM -48 0 40
3 2013-07-09 13:52 PM 49 0 12
3 2013-07-22 13:51 PM 11 0 14
1 2013-06-12 10:46 AM 63 0 7
3 2013-07-15 14:04 PM 33 0 13
2 2013-08-06 15:05 PM 39 0 42
2 2013-08-07 13:38 PM 30 0 43
I am looking to order this table by CardNo and then TransNo but i only want the query to display the record with the highest TransNo for each CardNo. In other words discard the records with the lower TransNo for each CardNo.
My desired result should hopefully look something like this:
CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-12 10:46 AM 63 0 7
2 2013-08-07 13:38 PM 30 0 43
3 2013-07-22 13:51 PM 11 0 14
I am using SQL 2012 Express but would also like this query to work in SQL 2005.
View 7 Replies
View Related
Jul 23, 2005
Hello everyone,I'm trying to solve this problem but can't seem to figure out how tostart. I would like to create a rating system where people can vote(1-5 stars) on randomly displayed items. The randomly displayed itemsshould either have very high ratings OR a very low number of ratings.For example, only return items in the top 20th percentile *OR* itemswith fewer than 5 votes.The question is, how would I write an SQL query to return such aresult? Is it even possible? Should this be handled by my applicationrather than the database?For simplicity, let's assume I have the following table:tbl_items-----------------item_iditem_nameavg_ratingnum_votes-----------------Any help or pointers in the right direction would be greatlyappreciated. My apologies in advance if the solution is obvious and Iam clearly missing the point ;-)
View 5 Replies
View Related
Sep 4, 2014
I have a warehouse table but I don't know which query will update warehouse inside of information ? Thus, how to write a query list all query have include this warehouse table name in there ?
View 1 Replies
View Related
Jan 18, 2006
I've tried everything I can think of to find all the records in a table column (lastname) that contain an apostrophe. I know they are there (O'Brian, D'Marcus, etc.) However, I keep getting syntax errors.
Could someone PLEASE help?!!
Thanks,
Karen
View 3 Replies
View Related
Aug 30, 2007
Hi,
I need to a find table which is used by list of stored procedures.
Can you please send me the query which is used?
Thanks and Regards
Abdul M.G
View 4 Replies
View Related
Apr 9, 2015
I need to find the history in SQl server.
I need to find the data, When was data last inserted/updated/deleted in a specific table?. is it possible?
Table name: Employee
View 1 Replies
View Related
Nov 6, 2007
Hi,
Does anyone know how to find out how many rows have been updated or deleted in a particular table for the last 1 hour?
Please reply.
Best Regards,
Ansaar
View 5 Replies
View Related
Oct 25, 2012
Table structure is very simple as below and I know there are solutions with joins (Left outer joins), need to know if it is possible to get o/p without using joins
Note:- also need records who doesn't have manager (null)
table structure
eid------ename------mgrid
1------Nancy------2
2------Andrew------null
3------Janet ------2
4------Margaret------2
5------Steven------4
6------Michael ------5
o/p
Employee------Manager
Nancy------Andrew
Andrew------Null
Janet ------Andrew
.
.
View 9 Replies
View Related
Mar 9, 2008
I have to write some reports for a database I am not familiar with. Is there a query I can use to find a table name if I know the field name?
example: Select table_name from database where field_name = 'my_field'
Mike
View 1 Replies
View Related
Oct 20, 2015
Grade Table is:
Grade_letter lowbound_marks uperbound_marks
A 85 100
B 60 84
C 0 59
Suppose some another query returns marks.Now, I am stuck with the query which takes the grade_letter based on the marks falling in the range given above. I want a query which is not hardcoded and calculates the grade based on the values given in the table only.
View 4 Replies
View Related
Nov 7, 2014
I have the following Games table:
CREATE TABLE [dbo].[Games](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Lge] [nvarchar](255) NULL,
[GameDate] [date] NULL,
[HomeTeam] [nvarchar](255) NULL,
[Home_Score] [float] NULL,
[AwayTeam] [nvarchar](255) NULL,
[Away_Score] [float] NULL)
with the following data:
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
[Code] ....
This gives the standings as:
Team B4 - 1 -
Team C1 - 1 1.5
Team A2 - 5 3
How can I query the data to find the "games behind" at any date?
View 9 Replies
View Related
Mar 30, 2006
which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query
View 10 Replies
View Related
Jun 12, 2008
I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
View 5 Replies
View Related
Nov 25, 2007
I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily.
this section shows the problem, i am loading xml from a file and trying to insert this into the sql database
XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));reader.WhitespaceHandling = WhitespaceHandling.None;XmlDocument xmlDocF = new XmlDocument();xmlDocF.Load(reader);this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;this.SqlDataSource1.Update();reader.Close();
pls help
View 6 Replies
View Related
Jul 25, 2000
I'd like some ideas on how to help out my VB staff when it comes to the tick ( ' ) mark being used in front end applications. When users enter data into the front end, and they include tick marks (eg: Sam's Auto Store), this causes trouble in how the app uses that data in a WHERE clause and other places. Does anyone have any slick ideas or experience in handling this. We have experimented with search & replace, double tick marks ( '' ), etc...
View 1 Replies
View Related
Jan 10, 2005
i'm currently a student in computer information sciences, and i wanted to give my studying some more focus thus choosing dba. now i'd like to dba but be as broad as possible when i first start out my career. i know some programming languages and recently finished a C# book for doing .net development and a ASP.net book for scripting. but where should i go from here? should i just start studying ms sql server? what about a microsoft certification? i'm very curious to know where the next step in my studying should be, so anyone who was in my shoes, i'd love to hear any information you have to offer. TIA
View 14 Replies
View Related
May 3, 2006
I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website. The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site. These announcements can be clicked on to display further detail. When announcements are clicked on a javascript popup window is generated that displays the strings. All data is stored in a SQL Server DB.What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code? (Though it seems I can't display an apostrophe, even when using the HTML code ''')If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.
View 7 Replies
View Related
Feb 5, 2008
I am working on a project to import information from one database table to another. This info includes names, addresses, etc. I am running into a challenge whenever one of the names, such as O'Brien, causes problems in the sql statement. Is there a way for me to tell it to ignore the ' in O'Brien?
Thanks,
Brian
View 8 Replies
View Related
Jul 23, 2005
Hi,I'm finishing up a beginning SQL class where we learned on an Oracledatabase and the transition to working on SQL Server is easy. The next moreadvanced course will be in PL/SQL, but I know I will be working on SQLServer in the workplace, so my question is if I should take this course.Will I benefit from the basic philosophies that will be covered, or will itjust make a transition for me more difficult? Will it be partly a waste oftime and money and I'd be better served getting a book and self teachingmyself? I know that in a greater sense learning something isn't necessarilya waste, but I mean from the perspective of my goal of being able to use SqlServer, will this course be useful?thanks.
View 10 Replies
View Related