Database

SELECT RIGHT('0000000' + @STR, CASE WHEN LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) < 7 THEN 7 ELSE LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) END )

,RIGHT('0000000' + dbo.SD.PRODUCT, CASE WHEN LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) < 7 THEN
        7 ELSE LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) END)

DECLARE @INPUT NVARCHAR(MAX) = 'your product code with or without leading zeros goes here'
DECLARE @TMP_INPUT INT = CAST(@INPUT AS INT)

SELECT
 CASE
  WHEN LEN(CAST(@TMP_INPUT AS NVARCHAR)) < 7 THEN SUBSTRING(@INPUT, 1, 7)
  ELSE CAST(@TMP_INPUT AS NVARCHAR)
 END

studentno    subjectid    mark
20              1          50
20              2          60
20              3          70
20              4          60
21              1          70
...            ...        ...

SELECT s.subjectname, m.mark FROM marks m inner join subjects s
on m.subjectid = s.subjectid WHERE studentno = 20

Table ExamType
    ExamTypeId           (Primary Key)
    ExamTypeName         (Exam I / Exam II / Exam III)
 
Table Subject
    SubjectId            (Primary Key)
    SubjectName          (English / Geography / ...)

Table Student
    StudentId            (Primary Key)
    StudentName

Table Exam
    ExamId               (Primary Key)
    ExamTypeId           (Foreign Key referencing ExamType.ExamTypeId)
    SubjectId            (Foreign Key referencing Subject.SubjectId)
    ExamTakenDate

Table Mark
    ExamId               (Foreign Key referencing Exam.ExamId)       \_ Primary Key
    StudentId            (Foreign Key referencing Student.StudentId) /
    Score

SELECT St.StudentName,
       Sj.SubjectName,
       Et.ExamTypeName,
       Ex.ExamTakenDate,
       Mk.Score
FROM Student AS St
JOIN Mark AS Mk ON St.StudentId = Mk.StudentId
JOIN Exam AS Ex ON Mk.ExamId = Ex.ExamId
JOIN ExamType AS Et ON Ex.ExamTypeId = Et.ExamTypeId
JOIN Subject AS Sj ON Ex.SubjectId = Sj.SubjectId
WHERE
     StudentId = ...   /* however you want to narrow your query result */
     ExamId = ...
     SubjectId = ...

Ludwing RS wrote:

I have a query and it takes too much time to fetch

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

create index myTable_num_fnix
on myTable (substr(NUM,0,3),substr(NUM,3,15));

delgaillo wrote:

Yes, I have tried with sql compare of redgate and the problem is the same

SELECT RoomNo
FROM RESERVATION
ORDER BY HotelNo

SELECT HotelNo, RoomNo FROM Room WHERE RoomNo NOT IN (SELECT RoomNo FROM Reservation) ORDER BY HotelNo, RoomNo