Jump to content

SQL test


4Vikram

Recommended Posts

A database has > 1000 tables and there are about few hundred tables that have column called "CustomerID". Write a query to get the list of tables that have 1 more or rows for CustomerID = 100.(dynamic SQL)

2) Given a sequence A-01 to A-099 find the missing sequence(use dynamic or T-sql)(the numbers are in string how do ypu find it)

3)  how do you enforce rules by giving an example

 

any one?

Link to comment
Share on other sites

6 minutes ago, 4Vikram said:

A database has > 1000 tables and there are about few hundred tables that have column called "CustomerID". Write a query to get the list of tables that have 1 more or rows for CustomerID = 100.(dynamic SQL)

2) Given a sequence A-01 to A-099 find the missing sequence(use dynamic or T-sql)(the numbers are in string how do ypu find it)

3)  how do you enforce rules by giving an example

 

any one?

Off topic, how are you? Long time

Ltt

Link to comment
Share on other sites

1 minute ago, kingcasanova said:

daani tharuvaatha madhyalo konchem theleedu, tharuvaatha where ayithe pakka vasthundi

Eppudoo 2011 lo MCA chadivetappudu nerchukunna working in non IT em gurthundi sasthaai

Link to comment
Share on other sites

1 hour ago, 4Vikram said:

A database has > 1000 tables and there are about few hundred tables that have column called "CustomerID". Write a query to get the list of tables that have 1 more or rows for CustomerID = 100.(dynamic SQL)

2) Given a sequence A-01 to A-099 find the missing sequence(use dynamic or T-sql)(the numbers are in string how do ypu find it)

3)  how do you enforce rules by giving an example

 

any one?

you mean, "1 or more rows for customerid = 100"

Link to comment
Share on other sites

1 hour ago, 4Vikram said:

A database has > 1000 tables and there are about few hundred tables that have column called "CustomerID". Write a query to get the list of tables that have 1 more or rows for CustomerID = 100.(dynamic SQL)

2) Given a sequence A-01 to A-099 find the missing sequence(use dynamic or T-sql)(the numbers are in string how do ypu find it)

3)  how do you enforce rules by giving an example

 

any one?

1st dhaaniki idhi work avutundhemo chudu:

 

 

select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' || owner || '.'|| table_name || ' where empserialnum = ''3G5743''' )),'/ROWSET/ROW/c') )as count from all_tables 
where owner = 'SCHEMA_NAME' and table_name like '%ABC_TABLE%'
Link to comment
Share on other sites

1 hour ago, 4Vikram said:

A database has > 1000 tables and there are about few hundred tables that have column called "CustomerID". Write a query to get the list of tables that have 1 more or rows for CustomerID = 100.(dynamic SQL)

 

you have to do it on top level schema or sys.tables

SELECT      COLUMN_NAME AS 'CustomerID '          
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%CustomerID %' 

give a try

 

first fetch all data and save in #temptable than filter #temptable. 

Jai Balaya

Link to comment
Share on other sites

assuming SQL server:

1)  select  distinct TABLE_SCHEMA+'.'+ TABLE_NAME  from information_schema.columns where column_name = 'CustomerID'

2) is this information in a table column?

3) not sure what this means

 

Link to comment
Share on other sites

1 hour ago, 4Vikram said:

A database has > 1000 tables and there are about few hundred tables that have column called "CustomerID". Write a query to get the list of tables that have 1 more or rows for CustomerID = 100.(dynamic SQL)

2) Given a sequence A-01 to A-099 find the missing sequence(use dynamic or T-sql)(the numbers are in string how do ypu find it)

3)  how do you enforce rules by giving an example

 

any one?

Hey...how are you ? Been long time :)

Link to comment
Share on other sites

select owner, table_name from all_tab_columns where column_name = 'CustomerID ' group by owner, table_name having count(*) = 1 ; 

This needs to be enhanced to get column value =100 ( May be loop lo tables you get in stored proc); Owner = 'ABC' iste where clause lo only that schema lo istundi lekapote all schemas lo istundi customerID columns unna tables. 

Edo similar dani mida working so idea kosam just posted. Not final solution.

Link to comment
Share on other sites

1.Oracle lo procedure rayali in combo with all_tab_columns

2.write sql script to split data into A, 099 to 2 columns, sort by number column, then crreate another table with number column with all numbers, tableB minus table A gives gaps

Link to comment
Share on other sites

6 minutes ago, Katara said:

1.Oracle lo procedure rayali in combo with all_tab_columns

2.write sql script to split data into A, 099 to 2 columns, sort by number column, then crreate another table with number column with all numbers, tableB minus table A gives gaps

Same thing cheppanu ^^

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...